bpt.py scriptHistorical BPT data from 2006-2018 can be downloaded in the CMS website under Medicare/Medicare Advantage Rates & Statistics/Data. For each year 2014 and after, the bid data is standardized and includes the following zip files:
Data in earlier years store the data corresponding to each workbook as individual zip files. The functions in bpt.py should be able to preprocess those.
Within the MA zip file, there are serveral tab delimited text files with the prefix ma_X, where X is a number corresponding to a worksheet in the BPT workbook. Each number corresponds to the following fields:
ma_1: Base Period Experience and Projection Assumptionsma_2: MA Projected Allowed Costs PMPMma_3: MA Projected Cost Sharing PMPMma_4: MA Projected Revenue Requirement PMPMma_5_cnty_obsXXXXXX_XXXXXX_20XX: MA Benchmark PMPM by county, observations, and yearma_6: MA Bid Summaryma_7: Optional Supplemental BenefitsThere are several files that are required to analyze the bid data in its full context. The bpt.py module enables the user to access the bid data more easily, where all *.zip files in a single directory. The goal of the module was to save time on I/O to conduct faster data analyses.
To use this library out of the box, it is suggested that BPT data directory structure should be as followed:
data/
|- 20XX/
|- BPT20XX_ma_0.zip
The BPT class takes in a single paramter dir_to_query:str, which is a directory that can contain multiple objects.
Below are the main operations and the associated functions in the python script:
load_data_dict(dirpath: str) -> pd.DataFrame: Function that reads in the data dictionary containing human-readable field names and return a dataframe that maps the headers for each text file (ma_X) to the human-readable field.load_sheet(dirpath: str, objname:str) -> pd.DataFrame: Function that loads the zipped text files into pandas Dataframes.load_county_data(dirpath: str, objname:str="ma_5") -> pd.DataFrame: Function that reads in the benchmark data, which consists of the several text files with the ma_5_... prefix, as a single pandas dataframe.replace_field_to_name(meta: pd.DataFrame, df: pd.DataFrame) -> pd.DataFrame: Function that replaces the column headers (excel column locations) to human-readable column names.BPT: A class corresponding to a given year for the BPT data. Contains the year, meta data, and each workbook with the structure ma_X. Note that entries without a Bid ID are dropped.Let's load up the 2015 BPT data as an example.
from bpt import BPT
import pandas as pd
import numpy as np
dir_to_query = '/mnt/c/Users/ScottCampit_nzmndyg/Projects/bid-pricing-tool/data/2015/'
BPT2015 = BPT(dir_to_query)
LOADING 2015 BPT DATA
LOADING BASE PERIOD EXPERIENCE ('ma_1') DATA
LOADING PROJECTED ALLOWED COST ('ma_2') DATA
LOADING PROJECTED COST SHARING ('ma_3') DATA
LOADING PROJECTED REV REQUIREMENT ('ma_4') DATA
LOADING BENCHMARK ('ma_5') DATA
LOADING BID SUMMARY ('ma_6') DATA
LOADING OPTIONAL SUPPLEMENTAL BENEFITS ('ma_7') DATA
To view MA Bid Summary data (stored in ma_6), call the object in the BPT class. This will return a pandas dataframe that can be further refined (e.g. fillnas, ensure datatypes are correct, etc.)
pd.set_option('display.max_columns', None)
bid_summary = BPT2015.ma_6
bid_summary
| BID ID (H-number, Plan ID, Segment ID) | Contract Year (2015) | Version Number | Medicare-covered Net medical cost( MA Bid Summary!D0025) | Medicare-covered Non-medical expense( MA Bid Summary!D0027) | Medicare-covered Gain / loss margin( MA Bid Summary!D0028) | Total Medicare-covered revenue requirement( MA Bid Summary!D0029) | Plan A/B Bid Summary: Standardized A/B Benchmark( MA Bid Summary!D0031) | Plan A/B Bid Summary: Plan A/B Benchmark( MA Bid Summary!D0032) | Plan A/B Bid Summary: Non-ESRD Risk Factor( MA Bid Summary!D0033) | Plan A/B Bid Summary: Conversion Factor( MA Bid Summary!D0034) | Date Prepared( MA Bid Summary!D0055) | Maximum Pt B premium buydown amt., per CMS( MA Bid Summary!E0014) | Supplemental Net medical cost( MA Bid Summary!E0025) | Supplemental Non-medical expense( MA Bid Summary!E0027) | Supplemental Gain / loss margin( MA Bid Summary!E0028) | Total Supplemental revenue requirement( MA Bid Summary!E0029) | MA Rebate - Medical PMPM Alloc( MA Bid Summary!I0023) | PMPM Medical Allocation to Reduce A/B Cost Share( MA Bid Summary!I0025) | PMPM Medical Allocation to Other Mand Supp Benefits( MA Bid Summary!I0026) | PMPM Medical Allocation to Pt B Premium Buydown( MA Bid Summary!I0027) | PMPM Medical Allocation to Pt D Basic Premium Buydown( MA Bid Summary!I0028) | PMPM Medical Allocation to Pt D Suppl Premium Buydown( MA Bid Summary!I0029) | PMPM Medical Allocation to Total( MA Bid Summary!I0030) | MA Rebate - PMPM Admin Allocation( MA Bid Summary!J0023) | PMPM Admin Allocation to Reduce A/B Cost Share( MA Bid Summary!J0025) | PMPM Admin Allocation to Other Mand Supp Benefits( MA Bid Summary!J0026) | PMPM Admin Allocation to Pt B Premium Buydown( MA Bid Summary!J0027) | PMPM Admin Allocation to Pt D Basic Premium Buydown( MA Bid Summary!J0028) | PMPM Admin Allocation to Pt D Suppl Premium Buydown( MA Bid Summary!J0029) | PMPM Admin Allocation Total( MA Bid Summary!J0030) | MA Rebate - PMPM Allocation Gain/Loss( MA Bid Summary!K0023) | Reduce A/B Cost Share - PMPM Allocation Gain/Loss( MA Bid Summary!K0025) | Other Mandatory Supplemental Benefits - PMPM Allocation Gain/Loss( MA Bid Summary!K0026) | Pt B Premium Buydown - PMPM Allocation Gain/Loss( MA Bid Summary!K0027) | Pt D Basic Premium Buydown - PMPM Allocation Gain/Loss( MA Bid Summary!K0028) | Pt D Suppl Premium Buydown - PMPM Allocation Gain/Loss( MA Bid Summary!K0029) | Total PMPM Allocation Gain/Loss( MA Bid Summary!K0030) | PMPM Rebate Allocation for Part B Premium( MA Bid Summary!L0013) | Part B Rebate Allocation - rounded to one decimal( MA Bid Summary!L0014) | Total PMPM Allocation to MA Rebate( MA Bid Summary!L0023) | Total PMPM Allocation to Reduce A/B Cost Share( MA Bid Summary!L0025) | Total PMPM Allocation to Other Mand Supp Benefits( MA Bid Summary!L0026) | Total PMPM Allocation to Pt B Premium Buydown( MA Bid Summary!L0027) | Total PMPM Allocation to Pt D Basic Premium Buydown( MA Bid Summary!L0028) | Total PMPM Allocation to Pt D Suppl Premium Buydown( MA Bid Summary!L0029) | Total PMPM Allocation( MA Bid Summary!L0030) | MA Unallocated rebate( MA Bid Summary!L0031) | Max Value for Reduce A/B Cost Share( MA Bid Summary!M0025) | Max Value for Other Mand Supp Benefits( MA Bid Summary!M0026) | Max Value for Pt B Premium Buydown( MA Bid Summary!M0027) | Other Information - Rebate Allocations Reduce A/B Cost Sharing (max. value=$0.00)( MA Bid Summary!R0013) | Other Information - Rebate Allocations Other A/B Mand Suppl Benefits (max. value=$0.00)( MA Bid Summary!R0014) | Estimated Plan Premium A/B Mandatory Supplemental revenue requirements( MA Bid Summary!R0022) | Estimated Plan Premium Less A/B Cost Sharing( MA Bid Summary!R0024) | Estimated Plan Premium Less Other Mand Supplemental Benefits( MA Bid Summary!R0025) | Estimated Plan Premium A/B Mandatory Supplemental Premium( MA Bid Summary!R0027) | Estimated Plan Premium Basic MA Premium( MA Bid Summary!R0029) | Estimated Plan Premium MA Enrollee Premium( MA Bid Summary!R0030) | Estimated Plan Premium Rounded MA Premium Subtotal( MA Bid Summary!R0031) | Estimated Plan Premium A/B rebates allocated to Pt D Basic Premium( MA Bid Summary!R0035) | Estimated Plan Premium A/B rebates for Pt D Basic Premium( MA Bid Summary!R0036) | Estimated Plan Premium A/B Rebates allocated to Pt D Suppl Premium( MA Bid Summary!R0041) | Estimated Plan Premium A/b Rebates for Pt D Suppl Premium( MA Bid Summary!R0042) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | H0028_001_0 | 2015 | 3 | 473.2208 | 103.6732 | -15.27038499 | 561.6236 | 733.0343 | 668.4790 | 0.913336 | 0.911934 | 8/1/2014 | 104.9 | 26.8559 | 5.8836 | -0.86661501 | 31.8729 | NaN | 18.9921 | 7.8529774 | 0.0 | 25.5 | 12.1 | 64.445 | NaN | 4.1608 | 1.720431 | NaN | NaN | NaN | 5.8812 | NaN | -0.6129 | -0.253408117 | NaN | NaN | NaN | -0.8663 | 0.0 | 0.0 | 69.46 | 22.54 | 9.32 | 0.0 | 25.5 | 12.1 | 69.46 | 0 | 22.54 | 9.33 | 104.9 | 22.54 | 9.32 | 31.87 | -22.54 | -9.32 | 0.01 | 0.0 | 0.01 | 0.0 | 25.5 | 25.5 | 12.1 | 12.1 |
| 1 | H0028_002_0 | 2015 | 3 | 537.4973 | 104.6150 | -48.05239067 | 594.0599 | 763.8931 | 701.8609 | 0.921151 | 0.918795 | 8/1/2014 | 104.9 | 28.3848 | 5.5246 | -2.537609328 | 31.3718 | NaN | 20.5748 | 7.808306902 | 0.0 | 32.2 | 6.5 | 67.0832 | NaN | 4.0046 | 1.519758 | NaN | NaN | NaN | 5.5243 | NaN | -1.8394 | -0.698064508 | NaN | NaN | NaN | -2.5375 | 0.0 | 0.0 | 70.07 | 22.74 | 8.63 | 0.0 | 32.2 | 6.5 | 70.07 | 0 | 22.74 | 8.63 | 104.9 | 22.74 | 8.63 | 31.37 | -22.74 | -8.63 | 0.00 | 0.0 | 0.00 | 0.0 | 32.2 | 32.2 | 6.5 | 6.5 |
| 2 | H0028_003_0 | 2015 | 3 | * | 101.4875 | * | 711.8527 | 767.3700 | 793.8604 | 1.038901 | 1.034521 | 8/1/2014 | 104.9 | * | 1.9828 | * | 13.9075 | NaN | * | * | 0.0 | 37.5 | 1.9 | * | NaN | 0.8925 | 1.090646 | NaN | NaN | NaN | 1.9831 | NaN | * | * | NaN | NaN | NaN | * | 0.0 | 0.0 | 53.31 | 6.26 | 7.65 | 0.0 | 37.5 | 1.9 | 53.31 | 0 | 6.26 | 7.65 | 104.9 | 6.26 | 7.65 | 13.91 | -6.26 | -7.65 | 0.00 | 0.0 | 0.00 | 0.0 | 37.5 | 37.5 | 1.9 | 1.9 |
| 3 | H0028_004_0 | 2015 | 3 | 448.1032 | 87.0015 | -26.16840231 | 508.9363 | 748.7868 | 650.7809 | 0.878000 | 0.869114 | 8/1/2014 | 104.9 | 61.7365 | 11.9865 | -3.605297693 | 70.1177 | NaN | 50.1252 | 11.59579136 | 0.0 | 7.0 | 15.1 | 83.821 | NaN | 9.7321 | 2.251382 | NaN | NaN | NaN | 11.9834 | NaN | -2.9272 | -0.677172861 | NaN | NaN | NaN | -3.6044 | 0.0 | 0.0 | 92.20 | 56.93 | 13.17 | 0.0 | 7.0 | 15.1 | 92.20 | 0 | 56.93 | 13.19 | 104.9 | 56.93 | 13.17 | 70.12 | -56.93 | -13.17 | 0.02 | 0.0 | 0.02 | 0.0 | 7.0 | 7.0 | 15.1 | 15.1 |
| 4 | H0028_801_0 | 2015 | 3 | 520.2695 | 63.3785 | -22.98709543 | 560.6609 | 750.6383 | 741.2694 | 0.988836 | 0.987519 | 7/9/2014 | 104.9 | 108.9429 | 13.2713 | -4.81342868 | 117.4007 | NaN | 18.6427 | 90.29955327 | 0.0 | 0.0 | 0.0 | 108.9422 | NaN | 2.2710 | 11.000157 | NaN | NaN | NaN | 13.2712 | NaN | -0.8237 | -3.98971 | NaN | NaN | NaN | -4.8134 | 0.0 | 0.0 | 117.40 | 20.09 | 97.31 | 0.0 | 0.0 | 0.0 | 117.40 | 0 | 20.09 | 97.31 | 104.9 | 20.09 | 97.31 | 117.40 | -20.09 | -97.31 | 0.00 | 0.0 | 0.00 | 0.0 | NaN | 0.0 | NaN | 0.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 3541 | R7444_001_0 | 2015 | 2 | 613.9036 | 89.7746 | 60.71346891 | 764.3917 | 789.2402 | 775.6206 | 0.986331 | 0.982743 | 8/4/2014 | 104.9 | 13.9719 | 2.0432 | 1.381784099 | 17.3969 | NaN | 5.8628 | 0 | 0.0 | 0.0 | 0.0 | 5.8628 | NaN | 0.8574 | 0.000000 | NaN | NaN | NaN | 0.8574 | NaN | 0.5798 | 0 | NaN | NaN | NaN | 0.5798 | 0.0 | 0.0 | 7.30 | 7.30 | 0.00 | 0.0 | 0.0 | 0.0 | 7.30 | 0 | 9.23 | 8.17 | 104.9 | 7.30 | 0.00 | 17.40 | -7.30 | 0.00 | 10.10 | 0.0 | 10.10 | 10.1 | 0.0 | 0.0 | 0.0 | 0.0 |
| 3542 | R9896_008_0 | 2015 | 4 | 1208.7383 | 125.8367 | 268.6700061 | 1603.2450 | 733.5164 | 1665.9317 | 2.273393 | 2.271158 | 8/4/2014 | 104.9 | 23.6332 | 2.4604 | 5.253025215 | 31.3466 | NaN | 7.7278 | 15.90797322 | 0.0 | 0.0 | 0.0 | 23.6358 | NaN | 0.8045 | 1.656112 | NaN | NaN | NaN | 2.4606 | NaN | 1.7177 | 3.535914431 | NaN | NaN | NaN | 5.2536 | 0.0 | 0.0 | 31.35 | 10.25 | 21.10 | 0.0 | 0.0 | 0.0 | 31.35 | 0 | 10.25 | 21.10 | 104.9 | 10.25 | 21.10 | 31.35 | -10.25 | -21.10 | 0.00 | 0.0 | 0.00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 3543 | R9896_009_0 | 2015 | 5 | 826.765 | 109.0657 | 93.08431431 | 1028.9150 | 733.5164 | 1138.0844 | 1.559652 | 1.551546 | 8/5/2014 | 104.9 | 31.1729 | 4.1123 | 3.509711688 | 38.7949 | NaN | 19.6945 | 11.47442172 | 0.0 | 15.8 | 0.0 | 46.969 | NaN | 2.5981 | 1.513689 | NaN | NaN | NaN | 4.1118 | NaN | 2.2174 | 1.291889038 | NaN | NaN | NaN | 3.5093 | 0.0 | 0.0 | 54.59 | 24.51 | 14.28 | 0.0 | 15.8 | 0.0 | 54.59 | 0 | 24.51 | 14.28 | 104.9 | 24.51 | 14.28 | 38.79 | -24.51 | -14.28 | 0.00 | 0.0 | 0.00 | 0.0 | 15.8 | 15.8 | 0.0 | 0.0 |
| 3544 | R9896_012_0 | 2015 | 5 | 516.5867 | 87.6878 | 60.48050404 | 664.7550 | 733.5164 | 707.4190 | 0.969065 | 0.964421 | 8/5/2014 | 104.9 | 17.5129 | 2.9727 | 2.050365626 | 22.5360 | NaN | 12.426 | 4.15752976 | 0.0 | 0.0 | 0.0 | 16.5835 | NaN | 2.1092 | 0.705718 | NaN | NaN | NaN | 2.8150 | NaN | 1.4548 | 0.486751806 | NaN | NaN | NaN | 1.9415 | 0.0 | 0.0 | 21.34 | 15.99 | 5.35 | 0.0 | 0.0 | 0.0 | 21.34 | 0 | 15.99 | 6.55 | 104.9 | 15.99 | 5.35 | 22.54 | -15.99 | -5.35 | 1.20 | 0.0 | 1.20 | 1.2 | 0.0 | 0.0 | 0.0 | 0.0 |
| 3545 | R9896_021_0 | 2015 | 4 | 818.5822 | 111.2960 | 26.37684447 | 956.2550 | 733.5164 | 975.0843 | 1.330701 | 1.329329 | 8/5/2014 | 104.9 | 8.0674 | 1.0969 | 0.259953787 | 9.4243 | NaN | 1.1728 | 6.891035019 | 0.0 | 0.0 | 0.0 | 8.0638 | NaN | 0.1595 | 0.936918 | NaN | NaN | NaN | 1.0964 | NaN | 0.0378 | 0.222047046 | NaN | NaN | NaN | 0.2598 | 0.0 | 0.0 | 9.42 | 1.37 | 8.05 | 0.0 | 0.0 | 0.0 | 9.42 | 0 | 1.37 | 8.05 | 104.9 | 1.37 | 8.05 | 9.42 | -1.37 | -8.05 | 0.00 | 0.0 | 0.00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
3546 rows × 64 columns
Let's now get columns with cost sharing information.
cost_share_2015 = bid_summary.filter(like='Shar')
cost_share_2015
| PMPM Medical Allocation to Reduce A/B Cost Share( MA Bid Summary!I0025) | PMPM Admin Allocation to Reduce A/B Cost Share( MA Bid Summary!J0025) | Reduce A/B Cost Share - PMPM Allocation Gain/Loss( MA Bid Summary!K0025) | Total PMPM Allocation to Reduce A/B Cost Share( MA Bid Summary!L0025) | Max Value for Reduce A/B Cost Share( MA Bid Summary!M0025) | Other Information - Rebate Allocations Reduce A/B Cost Sharing (max. value=$0.00)( MA Bid Summary!R0013) | Estimated Plan Premium Less A/B Cost Sharing( MA Bid Summary!R0024) | |
|---|---|---|---|---|---|---|---|
| 0 | 18.9921 | 4.1608 | -0.6129 | 22.54 | 22.54 | 22.54 | -22.54 |
| 1 | 20.5748 | 4.0046 | -1.8394 | 22.74 | 22.74 | 22.74 | -22.74 |
| 2 | * | 0.8925 | * | 6.26 | 6.26 | 6.26 | -6.26 |
| 3 | 50.1252 | 9.7321 | -2.9272 | 56.93 | 56.93 | 56.93 | -56.93 |
| 4 | 18.6427 | 2.2710 | -0.8237 | 20.09 | 20.09 | 20.09 | -20.09 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 3541 | 5.8628 | 0.8574 | 0.5798 | 7.30 | 9.23 | 7.30 | -7.30 |
| 3542 | 7.7278 | 0.8045 | 1.7177 | 10.25 | 10.25 | 10.25 | -10.25 |
| 3543 | 19.6945 | 2.5981 | 2.2174 | 24.51 | 24.51 | 24.51 | -24.51 |
| 3544 | 12.426 | 2.1092 | 1.4548 | 15.99 | 15.99 | 15.99 | -15.99 |
| 3545 | 1.1728 | 0.1595 | 0.0378 | 1.37 | 1.37 | 1.37 | -1.37 |
3546 rows × 7 columns
We can do some data quality checks and data cleaning.
For the following example, we will examine the BPT projection assumptions or projection factor. These projection factors are values that are used to project the base period data to the contract period.
The expected values should be a positive floating point value. None of the entries should be zero (but surprisingly, there are, and there's no documentation why that is!)
For instance, there are asterisks in some of the row entries. Per the 2015 Bid Pricing Tool Technical Notes:
"Any information that could be used to identify Medicare beneficiaries or other individuals. As part of this exclusion, an [asterisk] indicates that a data field has been suppressed because there are fewer than 11 Medicare beneficiaries in the data field or for necessary complimentary cell suppression."
Additionally, there are entries where there is no entry at all, and thus the cell contains NaNs.
base_period = BPT2015.ma_1
First, get projection assumptions.
prj_asmpt_pattern = "BID ID|MA Base!J00(2[7-9]|3[0-9]|4[0-4])|MA Base!K00(2[7-9]|3[0-9]|4[0-4])|MA Base!L00(2[7-9]|3[0-9]|4[0-4])|MA Base!M00(2[7-9]|3[0-9]|4[0-4])|MA Base!N00(2[7-9]|3[0-9]|4[0-4])|MA Base!O00(2[7-9]|3[0-9]|4[0-4])|MA Base!P00(2[7-9]|3[0-9]|4[0-4])|MA Base!Q00(2[7-9]|3[0-9]|4[0-4])"
prj_asmpt = base_period.loc[:, base_period.columns.str.contains(pat=prj_asmpt_pattern, regex=True)]
prj_asmpt = prj_asmpt.set_index('BID ID (H-number, Plan ID, Segment ID)')
prj_asmpt.head(3)
/tmp/ipykernel_3832/4245737771.py:2: UserWarning: This pattern is interpreted as a regular expression, and has match groups. To actually get the groups, use str.extract. prj_asmpt = base_period.loc[:, base_period.columns.str.contains(pat=prj_asmpt_pattern, regex=True)]
| Inpatient Facility [Util/1000 Trend]( MA Base!J0027) | Skilled Nursing Facility [Util/1000 Trend]( MA Base!J0028) | Home Health [Util/1000 Trend]( MA Base!J0029) | Ambulance [Util/1000 Trend]( MA Base!J0030) | DME/Prosthetics/Supplies [Util/1000 Trend]( MA Base!J0031) | OP Facility - Emergency [Util/1000 Trend]( MA Base!J0032) | OP Facility - Surgery [Util/1000 Trend]( MA Base!J0033) | OP Facility - Other [Util/1000 Trend]( MA Base!J0034) | Professional [Util/1000 Trend]( MA Base!J0035) | Part B Rx [Util/1000 Trend]( MA Base!J0036) | Other Medicare Covered [Util/1000 Trend]( MA Base!J0037) | Transportation (Non-Covered) [Util/1000 Trend]( MA Base!J0038) | Dental (Non-Covered) [Util/1000 Trend]( MA Base!J0039) | Vision (Non-Covered) [Util/1000 Trend]( MA Base!J0040) | Hearing (Non-Covered) [Util/1000 Trend]( MA Base!J0041) | Health & Education [Util/1000 Trend]( MA Base!J0042) | Other Non-Covered [Util/1000 Trend]( MA Base!J0043) | COB/Subrg. [Util/1000 Trend]( MA Base!J0044) | Inpatient Facility [Benefit Plan Change]( MA Base!K0027) | Skilled Nursing Facility [Benefit Plan Change]( MA Base!K0028) | Home Health [Benefit Plan Change]( MA Base!K0029) | Ambulance [Benefit Plan Change]( MA Base!K0030) | DME/Prosthetics/Supplies [Benefit Plan Change]( MA Base!K0031) | OP Facility - Emergency [Benefit Plan Change]( MA Base!K0032) | OP Facility - Surgery [Benefit Plan Change]( MA Base!K0033) | OP Facility - Other [Benefit Plan Change]( MA Base!K0034) | Professional [Benefit Plan Change]( MA Base!K0035) | Part B Rx [Benefit Plan Change]( MA Base!K0036) | Other Medicare Covered [Benefit Plan Change]( MA Base!K0037) | Transportation (Non-Covered) [Benefit Plan Change]( MA Base!K0038) | Dental (Non-Covered) [Benefit Plan Change]( MA Base!K0039) | Vision (Non-Covered) [Benefit Plan Change]( MA Base!K0040) | Hearing (Non-Covered) [Benefit Plan Change]( MA Base!K0041) | Health & Education [Benefit Plan Change]( MA Base!K0042) | Other Non-Covered [Benefit Plan Change]( MA Base!K0043) | COB/Subrg. [Benefit Plan Change]( MA Base!K0044) | Inpatient Facility [Population Change]( MA Base!L0027) | Skilled Nursing Facility [Population Change]( MA Base!L0028) | Home Health [Population Change]( MA Base!L0029) | Ambulance [Population Change]( MA Base!L0030) | DME/Prosthetics/Supplies [Population Change]( MA Base!L0031) | OP Facility - Emergency [Population Change]( MA Base!L0032) | OP Facility - Surgery [Population Change]( MA Base!L0033) | OP Facility - Other [Population Change]( MA Base!L0034) | Professional [Population Change]( MA Base!L0035) | Part B Rx [Population Change]( MA Base!L0036) | Other Medicare Covered [Population Change]( MA Base!L0037) | Transportation (Non-Covered) [Population Change]( MA Base!L0038) | Dental (Non-Covered) [Population Change]( MA Base!L0039) | Vision (Non-Covered) [Population Change]( MA Base!L0040) | Hearing (Non-Covered) [Population Change]( MA Base!L0041) | Health & Education [Population Change]( MA Base!L0042) | Other Non-Covered [Population Change]( MA Base!L0043) | COB/Subrg. [Population Change]( MA Base!L0044) | Inpatient Facility [Other Factor]( MA Base!M0027) | Skilled Nursing Facility [Other Factor]( MA Base!M0028) | Home Health [Other Factor]( MA Base!M0029) | Ambulance [Other Factor]( MA Base!M0030) | DME/Prosthetics/Supplies [Other Factor]( MA Base!M0031) | OP Facility - Emergency [Other Factor]( MA Base!M0032) | OP Facility - Surgery [Other Factor]( MA Base!M0033) | OP Facility - Other [Other Factor]( MA Base!M0034) | Professional [Other Factor]( MA Base!M0035) | Part B Rx [Other Factor]( MA Base!M0036) | Other Medicare Covered [Other Factor]( MA Base!M0037) | Transportation (Non-Covered) [Other Factor]( MA Base!M0038) | Dental (Non-Covered) [Other Factor]( MA Base!M0039) | Vision (Non-Covered) [Other Factor]( MA Base!M0040) | Hearing (Non-Covered) [Other Factor]( MA Base!M0041) | Health & Education [Other Factor]( MA Base!M0042) | Other Non-Covered [Other Factor]( MA Base!M0043) | COB/Subrg. [Other Factor]( MA Base!M0044) | Inpatient Facility [Unit Cost/Provider Payment Change]( MA Base!N0027) | Skilled Nursing Facility [Unit Cost/Provider Payment Change]( MA Base!N0028) | Home Health [Unit Cost/Provider Payment Change]( MA Base!N0029) | Ambulance [Unit Cost/Provider Payment Change]( MA Base!N0030) | DME/Prosthetics/Supplies [Unit Cost/Provider Payment Change]( MA Base!N0031) | OP Facility - Emergency [Unit Cost/Provider Payment Change]( MA Base!N0032) | OP Facility - Surgery [Unit Cost/Provider Payment Change]( MA Base!N0033) | OP Facility - Other [Unit Cost/Provider Payment Change]( MA Base!N0034) | Professional [Unit Cost/Provider Payment Change]( MA Base!N0035) | Part B Rx [Unit Cost/Provider Payment Change]( MA Base!N0036) | Other Medicare Covered [Unit Cost/Provider Payment Change]( MA Base!N0037) | Transportation (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0038) | Dental (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0039) | Vision (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0040) | Hearing (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0041) | Health & Education [Unit Cost/Provider Payment Change]( MA Base!N0042) | Other Non-Covered [Unit Cost/Provider Payment Change]( MA Base!N0043) | COB/Subrg. [Unit Cost/Provider Payment Change]( MA Base!N0044) | Inpatient Facility [Unit Cost Adj/Other Factor]( MA Base!O0027) | Skilled Nursing Facility [Unit Cost Adj/Other Factor]( MA Base!O0028) | Home Health [Unit Cost Adj/Other Factor]( MA Base!O0029) | Ambulance [Unit Cost Adj/Other Factor]( MA Base!O0030) | DME/Prosthetics/Supplies [Unit Cost Adj/Other Factor]( MA Base!O0031) | OP Facility - Emergency [Unit Cost Adj/Other Factor]( MA Base!O0032) | OP Facility - Surgery [Unit Cost Adj/Other Factor]( MA Base!O0033) | OP Facility - Other [Unit Cost Adj/Other Factor]( MA Base!O0034) | Professional [Unit Cost Adj/Other Factor]( MA Base!O0035) | Part B Rx [Unit Cost Adj/Other Factor]( MA Base!O0036) | Other Medicare Covered [Unit Cost Adj/Other Factor]( MA Base!O0037) | Transportation (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0038) | Dental (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0039) | Vision (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0040) | Hearing (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0041) | Health & Education [Unit Cost Adj/Other Factor]( MA Base!O0042) | Other Non-Covered [Unit Cost Adj/Other Factor]( MA Base!O0043) | COB/Subrg. [Unit Cost Adj/Other Factor]( MA Base!O0044) | Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) | Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) | Home Health [Additive Adjustment Util/1000]( MA Base!P0029) | Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) | DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) | OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) | OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) | OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) | Professional [Additive Adjustment Util/1000]( MA Base!P0035) | Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) | Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) | Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) | Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) | Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) | Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) | Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) | Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) | Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) | Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) | Home Health [Additive Adjustment PMPM]( MA Base!Q0029) | Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) | DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) | OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) | OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) | OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) | Professional [Additive Adjustment PMPM]( MA Base!Q0035) | Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) | Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) | Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) | Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) | Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) | Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) | Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) | Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) | COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BID ID (H-number, Plan ID, Segment ID) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| H0028_001_0 | 1.041586 | 1.040309 | 1.085316 | 1.085316 | 1.085316 | 1.064892 | 1.058333 | 1.062038 | 1.034823 | 1.038227 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.965442 | 1.000000 | 1.0 | 0.992343 | 1.000000 | 1.0 | 0.987336 | 1.001475 | 1.002775 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.064938 | 1.111958 | 1.047501 | 1.056095 | 1.051343 | 1.025000 | 1.023171 | 1.021300 | 1.024814 | 1.023619 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.946205 | 1.029156 | 1.021117 | 1.021117 | 1.021117 | 0.968596 | 0.973498 | 0.970729 | 0.992119 | 0.991759 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.021212 | 1.029386 | 1.000603 | 1.000322 | 0.999001 | 1.027579 | 1.025603 | 1.026627 | 1.018506 | 1.020768 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.032320 | 1.073888 | 1.095386 | 1.093760 | 1.096505 | 1.014410 | 1.004024 | 1.011607 | 0.970527 | 0.910285 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.0 | 0.0 | 0 | 0.0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | -12643.60265 | 1908.905341 | 0.0 | 0.0 | 0 | 0.0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | -4.830103 | 0.46735 | NaN |
| H0028_002_0 | 1.048467 | 1.033212 | 1.110564 | 1.110564 | 1.110564 | 1.078348 | 1.068757 | 1.072278 | 1.039763 | 1.051768 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.965887 | 1.000000 | 1.0 | 0.992552 | 1.000000 | 1.0 | 0.988270 | 1.000268 | 1.003637 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.086141 | 1.198642 | 1.073244 | 1.146072 | 1.070351 | 1.074450 | 1.025969 | 1.033564 | 1.032510 | 1.024794 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.946038 | 1.035478 | 1.010779 | 1.010779 | 1.010779 | 0.993476 | 0.991236 | 0.992058 | 0.984504 | 0.990402 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.011443 | 1.029042 | 1.007127 | 1.005176 | 1.004994 | 1.023802 | 1.022753 | 1.020853 | 1.017948 | 1.018910 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.034544 | 1.067340 | 1.106669 | 1.098973 | 1.101808 | 1.011492 | 0.996657 | 1.008320 | 0.981439 | 0.923308 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.0 | 0.0 | 0 | 0.0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | -2860.545342 | 2832.475088 | 0.0 | 0.0 | 0 | 0.0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0 | 0 | 0 | 0 | -1.221185 | 0.56387 | NaN |
| H0028_003_0 | 1.035891 | 1.045731 | 1.096176 | 1.096176 | 1.096176 | 1.068046 | 1.064381 | 1.065199 | 1.049049 | 1.062640 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.983970 | 1.038864 | 1.0 | 0.985140 | 1.010346 | 1.0 | 0.999905 | 1.003564 | 0.996684 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.126589 | 1.154194 | 1.011559 | 1.125635 | 1.068715 | 1.053648 | 1.022186 | 1.021385 | 1.015208 | 1.018191 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.949851 | 1.034484 | 1.014373 | 1.014373 | 1.014373 | 1.006236 | 1.002903 | 1.003647 | 0.988906 | 1.003002 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.014657 | 1.029904 | 1.005337 | 1.004158 | 1.004711 | 1.028985 | 1.027605 | 1.025852 | 1.019485 | 1.024157 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 1.009003 | 1.077537 | 1.087700 | 1.080784 | 1.098513 | 0.991736 | 1.019309 | 0.992896 | 0.999983 | 0.955871 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | NaN | 0.0 | 0.0 | 0 | 0.0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0 | * | -81.000569 | 0 | 2213.447304 | -19070.36114 | 0.0 | 0.0 | 0 | 0.0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0 | * | -0.261382 | 0 | 1.697966 | -1.626392 | NaN |
Replace missing values.
prj_asmpt = prj_asmpt.replace('*', 1)
prj_asmpt = prj_asmpt.replace(np.nan, 1)
prj_asmpt = prj_asmpt.replace(0, 1)
prj_asmpt = prj_asmpt.apply(lambda x: pd.to_numeric(x), axis=1, errors='coerce')
prj_asmpt.head(3)
| Inpatient Facility [Util/1000 Trend]( MA Base!J0027) | Skilled Nursing Facility [Util/1000 Trend]( MA Base!J0028) | Home Health [Util/1000 Trend]( MA Base!J0029) | Ambulance [Util/1000 Trend]( MA Base!J0030) | DME/Prosthetics/Supplies [Util/1000 Trend]( MA Base!J0031) | OP Facility - Emergency [Util/1000 Trend]( MA Base!J0032) | OP Facility - Surgery [Util/1000 Trend]( MA Base!J0033) | OP Facility - Other [Util/1000 Trend]( MA Base!J0034) | Professional [Util/1000 Trend]( MA Base!J0035) | Part B Rx [Util/1000 Trend]( MA Base!J0036) | Other Medicare Covered [Util/1000 Trend]( MA Base!J0037) | Transportation (Non-Covered) [Util/1000 Trend]( MA Base!J0038) | Dental (Non-Covered) [Util/1000 Trend]( MA Base!J0039) | Vision (Non-Covered) [Util/1000 Trend]( MA Base!J0040) | Hearing (Non-Covered) [Util/1000 Trend]( MA Base!J0041) | Health & Education [Util/1000 Trend]( MA Base!J0042) | Other Non-Covered [Util/1000 Trend]( MA Base!J0043) | COB/Subrg. [Util/1000 Trend]( MA Base!J0044) | Inpatient Facility [Benefit Plan Change]( MA Base!K0027) | Skilled Nursing Facility [Benefit Plan Change]( MA Base!K0028) | Home Health [Benefit Plan Change]( MA Base!K0029) | Ambulance [Benefit Plan Change]( MA Base!K0030) | DME/Prosthetics/Supplies [Benefit Plan Change]( MA Base!K0031) | OP Facility - Emergency [Benefit Plan Change]( MA Base!K0032) | OP Facility - Surgery [Benefit Plan Change]( MA Base!K0033) | OP Facility - Other [Benefit Plan Change]( MA Base!K0034) | Professional [Benefit Plan Change]( MA Base!K0035) | Part B Rx [Benefit Plan Change]( MA Base!K0036) | Other Medicare Covered [Benefit Plan Change]( MA Base!K0037) | Transportation (Non-Covered) [Benefit Plan Change]( MA Base!K0038) | Dental (Non-Covered) [Benefit Plan Change]( MA Base!K0039) | Vision (Non-Covered) [Benefit Plan Change]( MA Base!K0040) | Hearing (Non-Covered) [Benefit Plan Change]( MA Base!K0041) | Health & Education [Benefit Plan Change]( MA Base!K0042) | Other Non-Covered [Benefit Plan Change]( MA Base!K0043) | COB/Subrg. [Benefit Plan Change]( MA Base!K0044) | Inpatient Facility [Population Change]( MA Base!L0027) | Skilled Nursing Facility [Population Change]( MA Base!L0028) | Home Health [Population Change]( MA Base!L0029) | Ambulance [Population Change]( MA Base!L0030) | DME/Prosthetics/Supplies [Population Change]( MA Base!L0031) | OP Facility - Emergency [Population Change]( MA Base!L0032) | OP Facility - Surgery [Population Change]( MA Base!L0033) | OP Facility - Other [Population Change]( MA Base!L0034) | Professional [Population Change]( MA Base!L0035) | Part B Rx [Population Change]( MA Base!L0036) | Other Medicare Covered [Population Change]( MA Base!L0037) | Transportation (Non-Covered) [Population Change]( MA Base!L0038) | Dental (Non-Covered) [Population Change]( MA Base!L0039) | Vision (Non-Covered) [Population Change]( MA Base!L0040) | Hearing (Non-Covered) [Population Change]( MA Base!L0041) | Health & Education [Population Change]( MA Base!L0042) | Other Non-Covered [Population Change]( MA Base!L0043) | COB/Subrg. [Population Change]( MA Base!L0044) | Inpatient Facility [Other Factor]( MA Base!M0027) | Skilled Nursing Facility [Other Factor]( MA Base!M0028) | Home Health [Other Factor]( MA Base!M0029) | Ambulance [Other Factor]( MA Base!M0030) | DME/Prosthetics/Supplies [Other Factor]( MA Base!M0031) | OP Facility - Emergency [Other Factor]( MA Base!M0032) | OP Facility - Surgery [Other Factor]( MA Base!M0033) | OP Facility - Other [Other Factor]( MA Base!M0034) | Professional [Other Factor]( MA Base!M0035) | Part B Rx [Other Factor]( MA Base!M0036) | Other Medicare Covered [Other Factor]( MA Base!M0037) | Transportation (Non-Covered) [Other Factor]( MA Base!M0038) | Dental (Non-Covered) [Other Factor]( MA Base!M0039) | Vision (Non-Covered) [Other Factor]( MA Base!M0040) | Hearing (Non-Covered) [Other Factor]( MA Base!M0041) | Health & Education [Other Factor]( MA Base!M0042) | Other Non-Covered [Other Factor]( MA Base!M0043) | COB/Subrg. [Other Factor]( MA Base!M0044) | Inpatient Facility [Unit Cost/Provider Payment Change]( MA Base!N0027) | Skilled Nursing Facility [Unit Cost/Provider Payment Change]( MA Base!N0028) | Home Health [Unit Cost/Provider Payment Change]( MA Base!N0029) | Ambulance [Unit Cost/Provider Payment Change]( MA Base!N0030) | DME/Prosthetics/Supplies [Unit Cost/Provider Payment Change]( MA Base!N0031) | OP Facility - Emergency [Unit Cost/Provider Payment Change]( MA Base!N0032) | OP Facility - Surgery [Unit Cost/Provider Payment Change]( MA Base!N0033) | OP Facility - Other [Unit Cost/Provider Payment Change]( MA Base!N0034) | Professional [Unit Cost/Provider Payment Change]( MA Base!N0035) | Part B Rx [Unit Cost/Provider Payment Change]( MA Base!N0036) | Other Medicare Covered [Unit Cost/Provider Payment Change]( MA Base!N0037) | Transportation (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0038) | Dental (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0039) | Vision (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0040) | Hearing (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0041) | Health & Education [Unit Cost/Provider Payment Change]( MA Base!N0042) | Other Non-Covered [Unit Cost/Provider Payment Change]( MA Base!N0043) | COB/Subrg. [Unit Cost/Provider Payment Change]( MA Base!N0044) | Inpatient Facility [Unit Cost Adj/Other Factor]( MA Base!O0027) | Skilled Nursing Facility [Unit Cost Adj/Other Factor]( MA Base!O0028) | Home Health [Unit Cost Adj/Other Factor]( MA Base!O0029) | Ambulance [Unit Cost Adj/Other Factor]( MA Base!O0030) | DME/Prosthetics/Supplies [Unit Cost Adj/Other Factor]( MA Base!O0031) | OP Facility - Emergency [Unit Cost Adj/Other Factor]( MA Base!O0032) | OP Facility - Surgery [Unit Cost Adj/Other Factor]( MA Base!O0033) | OP Facility - Other [Unit Cost Adj/Other Factor]( MA Base!O0034) | Professional [Unit Cost Adj/Other Factor]( MA Base!O0035) | Part B Rx [Unit Cost Adj/Other Factor]( MA Base!O0036) | Other Medicare Covered [Unit Cost Adj/Other Factor]( MA Base!O0037) | Transportation (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0038) | Dental (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0039) | Vision (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0040) | Hearing (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0041) | Health & Education [Unit Cost Adj/Other Factor]( MA Base!O0042) | Other Non-Covered [Unit Cost Adj/Other Factor]( MA Base!O0043) | COB/Subrg. [Unit Cost Adj/Other Factor]( MA Base!O0044) | Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) | Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) | Home Health [Additive Adjustment Util/1000]( MA Base!P0029) | Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) | DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) | OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) | OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) | OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) | Professional [Additive Adjustment Util/1000]( MA Base!P0035) | Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) | Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) | Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) | Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) | Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) | Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) | Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) | Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) | Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) | Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) | Home Health [Additive Adjustment PMPM]( MA Base!Q0029) | Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) | DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) | OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) | OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) | OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) | Professional [Additive Adjustment PMPM]( MA Base!Q0035) | Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) | Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) | Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) | Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) | Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) | Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) | Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) | Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) | COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BID ID (H-number, Plan ID, Segment ID) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| H0028_001_0 | 1.041586 | 1.040309 | 1.085316 | 1.085316 | 1.085316 | 1.064892 | 1.058333 | 1.062038 | 1.034823 | 1.038227 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.965442 | 1.000000 | 1.0 | 0.992343 | 1.000000 | 1.0 | 0.987336 | 1.001475 | 1.002775 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.064938 | 1.111958 | 1.047501 | 1.056095 | 1.051343 | 1.025000 | 1.023171 | 1.021300 | 1.024814 | 1.023619 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.946205 | 1.029156 | 1.021117 | 1.021117 | 1.021117 | 0.968596 | 0.973498 | 0.970729 | 0.992119 | 0.991759 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.021212 | 1.029386 | 1.000603 | 1.000322 | 0.999001 | 1.027579 | 1.025603 | 1.026627 | 1.018506 | 1.020768 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.032320 | 1.073888 | 1.095386 | 1.093760 | 1.096505 | 1.014410 | 1.004024 | 1.011607 | 0.970527 | 0.910285 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -12643.602650 | 1908.905341 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -4.830103 | 0.467350 | 1.0 |
| H0028_002_0 | 1.048467 | 1.033212 | 1.110564 | 1.110564 | 1.110564 | 1.078348 | 1.068757 | 1.072278 | 1.039763 | 1.051768 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.965887 | 1.000000 | 1.0 | 0.992552 | 1.000000 | 1.0 | 0.988270 | 1.000268 | 1.003637 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.086141 | 1.198642 | 1.073244 | 1.146072 | 1.070351 | 1.074450 | 1.025969 | 1.033564 | 1.032510 | 1.024794 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.946038 | 1.035478 | 1.010779 | 1.010779 | 1.010779 | 0.993476 | 0.991236 | 0.992058 | 0.984504 | 0.990402 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.011443 | 1.029042 | 1.007127 | 1.005176 | 1.004994 | 1.023802 | 1.022753 | 1.020853 | 1.017948 | 1.018910 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.034544 | 1.067340 | 1.106669 | 1.098973 | 1.101808 | 1.011492 | 0.996657 | 1.008320 | 0.981439 | 0.923308 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -2860.545342 | 2832.475088 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -1.221185 | 0.563870 | 1.0 |
| H0028_003_0 | 1.035891 | 1.045731 | 1.096176 | 1.096176 | 1.096176 | 1.068046 | 1.064381 | 1.065199 | 1.049049 | 1.062640 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.983970 | 1.038864 | 1.0 | 0.985140 | 1.010346 | 1.0 | 0.999905 | 1.003564 | 0.996684 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.126589 | 1.154194 | 1.011559 | 1.125635 | 1.068715 | 1.053648 | 1.022186 | 1.021385 | 1.015208 | 1.018191 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.949851 | 1.034484 | 1.014373 | 1.014373 | 1.014373 | 1.006236 | 1.002903 | 1.003647 | 0.988906 | 1.003002 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.014657 | 1.029904 | 1.005337 | 1.004158 | 1.004711 | 1.028985 | 1.027605 | 1.025852 | 1.019485 | 1.024157 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.009003 | 1.077537 | 1.087700 | 1.080784 | 1.098513 | 0.991736 | 1.019309 | 0.992896 | 0.999983 | 0.955871 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | -81.000569 | 1.0 | 2213.447304 | -19070.361140 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | -0.261382 | 1.0 | 1.697966 | -1.626392 | 1.0 |
Separate projection assumptions by category.
util_adj = prj_asmpt.filter(regex=('Util/1000 Trend|Benefit Plan Change|Population Change|/[Other Factor/]'), axis=1)
unitcost_adj = prj_asmpt.filter(regex='Unit Cost')
additive_adj = prj_asmpt.filter(regex='Additive Adjustment')
additive_adj.head(3)
| Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) | Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) | Home Health [Additive Adjustment Util/1000]( MA Base!P0029) | Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) | DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) | OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) | OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) | OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) | Professional [Additive Adjustment Util/1000]( MA Base!P0035) | Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) | Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) | Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) | Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) | Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) | Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) | Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) | Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) | Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) | Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) | Home Health [Additive Adjustment PMPM]( MA Base!Q0029) | Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) | DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) | OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) | OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) | OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) | Professional [Additive Adjustment PMPM]( MA Base!Q0035) | Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) | Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) | Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) | Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) | Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) | Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) | Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) | Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) | COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BID ID (H-number, Plan ID, Segment ID) | |||||||||||||||||||||||||||||||||||
| H0028_001_0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -12643.602650 | 1908.905341 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -4.830103 | 0.467350 | 1.0 |
| H0028_002_0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -2860.545342 | 2832.475088 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -1.221185 | 0.563870 | 1.0 |
| H0028_003_0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | -81.000569 | 1.0 | 2213.447304 | -19070.361140 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | -0.261382 | 1.0 | 1.697966 | -1.626392 | 1.0 |
If we wanted to, we can merge the bid data with other data sources such as Star Rating using the BID ID number. We'll demonstrate how to do that with the 2015 BPT data loaded above.
First, let's load the 2015 Star Rating data, which can be found here
2015_Star_Ratings_and_Display_Measures.zip2015 Star Ratings Fall Release (10_2014)/2015_Report_Card_Master_Table_2014_10_03_summary.csvThe functions below scrape Star Rating data from the CMS website.
import os
import requests
import zipfile
import io
from bs4 import BeautifulSoup as bs
BASEURL = 'https://www.cms.gov/'
def check_if_file_exists(url:str=None, data_directory:str=None, save_file:bool=True):
"""
Automatically checks whether or not the queried data is stored locally in the machine.
* If there is a record, then the file will not be saved.
* Otherwise, the file will be saved at the directory specified.
Note: if a directory is not supplied, the file will NOT be saved.
"""
if None not in (data_directory, url, save_file):
datastore = os.listdir(data_directory)
if url.split('/')[-1] not in datastore:
r = requests.get(url)
filename = url.split('/')[-1]
with open(data_directory+filename, 'wb') as file_:
file_.write(r.content)
else:
return
def get_star_rating(
STARPATH:str="Medicare/Prescription-Drug-Coverage/PrescriptionDrugCovGenIn/PerformanceData", \
SAVE_PATH:str=None,#Example: "C:/Users/ScottCampit/Projects/quadrant-chart/data/", \
year:str=None
):
"""Scrapes Star Rating data from the CMS website."""
r = requests.get(BASEURL+STARPATH)
soup = bs(r.text, 'html.parser')
all_data_urls = list()
for name in soup.findAll('a', href=True):
zipurl = name['href']
if zipurl.endswith('.zip'):
if any(filename in zipurl for filename in ('data', 'Data')):
if year:
if any(filename in zipurl for filename in (year)):
all_data_urls.append(zipurl)
else:
all_data_urls.append(zipurl)
# Get the latest zip file containing the star rating data.
latest_arxiv = all_data_urls[0]
new_r2 = requests.get(BASEURL+latest_arxiv)
check_if_file_exists(url=BASEURL+latest_arxiv, data_directory=SAVE_PATH)
arxiv = zipfile.ZipFile(io.BytesIO(new_r2.content), 'r')
for name in arxiv.namelist():
if 'Summary' in name: # The relevant .csv file we need
df = pd.read_csv(arxiv.open(name), skiprows=1)
df = df.dropna(axis=1, how='all')
return df
star2015 = get_star_rating(SAVE_PATH=None, year='2015')
star2015.head(3)
| Contract Number | Organization Type | Contract Name | Organization Marketing Name | Parent Organization | SNP | 2020 Disaster % | 2021 Disaster % | 2023 Part C Summary | 2023 Part D Summary | 2023 Overall | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | E3014 | Employer/Union Only Direct Contract PDP | PSERS HOP PROGRAM | Pennsylvania Public School Employees Retiremen... | Commonwealth of PA Pub Schools Retirement System | No | 100.0 | 1 | Not Applicable | 4.5 | Not Applicable |
| 1 | E4744 | Employer/Union Only Direct Contract PDP | MODOT/MSHP MEDICAL AND LIFE INSURANCE PLAN | MISSOURI DEPARTMENT OF TRANSPORTATION | Missouri Highways and Transportation Commission | No | 100.0 | 1 | Not Applicable | 3.5 | Not Applicable |
| 2 | H0022 | Demo | BUCKEYE COMMUNITY HEALTH PLAN, INC. | Buckeye Health Plan - MyCare Ohio | Centene Corporation | No | 100.0 | 0 | Not Applicable | Not enough data available | Not enough data available |
We can map Star Rating data by Contract Number to the bid ID. Unfortunately, we don't have higher resolution with the segment IDs.
star2015['Contract Number'] = star2015['Contract Number'].str.rstrip()
prj_asmpt['Contract ID'] = prj_asmpt.index.str.split(pat='_', n=1).str[0]
joined_prj_asmpt = pd.merge(star2015, prj_asmpt, how='inner', left_on='Contract Number', right_on='Contract ID')
joined_prj_asmpt = joined_prj_asmpt.drop(['Contract ID'], axis=1)
joined_prj_asmpt.head(3)
| Contract Number | Organization Type | Contract Name | Organization Marketing Name | Parent Organization | SNP | 2020 Disaster % | 2021 Disaster % | 2023 Part C Summary | 2023 Part D Summary | 2023 Overall | Inpatient Facility [Util/1000 Trend]( MA Base!J0027) | Skilled Nursing Facility [Util/1000 Trend]( MA Base!J0028) | Home Health [Util/1000 Trend]( MA Base!J0029) | Ambulance [Util/1000 Trend]( MA Base!J0030) | DME/Prosthetics/Supplies [Util/1000 Trend]( MA Base!J0031) | OP Facility - Emergency [Util/1000 Trend]( MA Base!J0032) | OP Facility - Surgery [Util/1000 Trend]( MA Base!J0033) | OP Facility - Other [Util/1000 Trend]( MA Base!J0034) | Professional [Util/1000 Trend]( MA Base!J0035) | Part B Rx [Util/1000 Trend]( MA Base!J0036) | Other Medicare Covered [Util/1000 Trend]( MA Base!J0037) | Transportation (Non-Covered) [Util/1000 Trend]( MA Base!J0038) | Dental (Non-Covered) [Util/1000 Trend]( MA Base!J0039) | Vision (Non-Covered) [Util/1000 Trend]( MA Base!J0040) | Hearing (Non-Covered) [Util/1000 Trend]( MA Base!J0041) | Health & Education [Util/1000 Trend]( MA Base!J0042) | Other Non-Covered [Util/1000 Trend]( MA Base!J0043) | COB/Subrg. [Util/1000 Trend]( MA Base!J0044) | Inpatient Facility [Benefit Plan Change]( MA Base!K0027) | Skilled Nursing Facility [Benefit Plan Change]( MA Base!K0028) | Home Health [Benefit Plan Change]( MA Base!K0029) | Ambulance [Benefit Plan Change]( MA Base!K0030) | DME/Prosthetics/Supplies [Benefit Plan Change]( MA Base!K0031) | OP Facility - Emergency [Benefit Plan Change]( MA Base!K0032) | OP Facility - Surgery [Benefit Plan Change]( MA Base!K0033) | OP Facility - Other [Benefit Plan Change]( MA Base!K0034) | Professional [Benefit Plan Change]( MA Base!K0035) | Part B Rx [Benefit Plan Change]( MA Base!K0036) | Other Medicare Covered [Benefit Plan Change]( MA Base!K0037) | Transportation (Non-Covered) [Benefit Plan Change]( MA Base!K0038) | Dental (Non-Covered) [Benefit Plan Change]( MA Base!K0039) | Vision (Non-Covered) [Benefit Plan Change]( MA Base!K0040) | Hearing (Non-Covered) [Benefit Plan Change]( MA Base!K0041) | Health & Education [Benefit Plan Change]( MA Base!K0042) | Other Non-Covered [Benefit Plan Change]( MA Base!K0043) | COB/Subrg. [Benefit Plan Change]( MA Base!K0044) | Inpatient Facility [Population Change]( MA Base!L0027) | Skilled Nursing Facility [Population Change]( MA Base!L0028) | Home Health [Population Change]( MA Base!L0029) | Ambulance [Population Change]( MA Base!L0030) | DME/Prosthetics/Supplies [Population Change]( MA Base!L0031) | OP Facility - Emergency [Population Change]( MA Base!L0032) | OP Facility - Surgery [Population Change]( MA Base!L0033) | OP Facility - Other [Population Change]( MA Base!L0034) | Professional [Population Change]( MA Base!L0035) | Part B Rx [Population Change]( MA Base!L0036) | Other Medicare Covered [Population Change]( MA Base!L0037) | Transportation (Non-Covered) [Population Change]( MA Base!L0038) | Dental (Non-Covered) [Population Change]( MA Base!L0039) | Vision (Non-Covered) [Population Change]( MA Base!L0040) | Hearing (Non-Covered) [Population Change]( MA Base!L0041) | Health & Education [Population Change]( MA Base!L0042) | Other Non-Covered [Population Change]( MA Base!L0043) | COB/Subrg. [Population Change]( MA Base!L0044) | Inpatient Facility [Other Factor]( MA Base!M0027) | Skilled Nursing Facility [Other Factor]( MA Base!M0028) | Home Health [Other Factor]( MA Base!M0029) | Ambulance [Other Factor]( MA Base!M0030) | DME/Prosthetics/Supplies [Other Factor]( MA Base!M0031) | OP Facility - Emergency [Other Factor]( MA Base!M0032) | OP Facility - Surgery [Other Factor]( MA Base!M0033) | OP Facility - Other [Other Factor]( MA Base!M0034) | Professional [Other Factor]( MA Base!M0035) | Part B Rx [Other Factor]( MA Base!M0036) | Other Medicare Covered [Other Factor]( MA Base!M0037) | Transportation (Non-Covered) [Other Factor]( MA Base!M0038) | Dental (Non-Covered) [Other Factor]( MA Base!M0039) | Vision (Non-Covered) [Other Factor]( MA Base!M0040) | Hearing (Non-Covered) [Other Factor]( MA Base!M0041) | Health & Education [Other Factor]( MA Base!M0042) | Other Non-Covered [Other Factor]( MA Base!M0043) | COB/Subrg. [Other Factor]( MA Base!M0044) | Inpatient Facility [Unit Cost/Provider Payment Change]( MA Base!N0027) | Skilled Nursing Facility [Unit Cost/Provider Payment Change]( MA Base!N0028) | Home Health [Unit Cost/Provider Payment Change]( MA Base!N0029) | Ambulance [Unit Cost/Provider Payment Change]( MA Base!N0030) | DME/Prosthetics/Supplies [Unit Cost/Provider Payment Change]( MA Base!N0031) | OP Facility - Emergency [Unit Cost/Provider Payment Change]( MA Base!N0032) | OP Facility - Surgery [Unit Cost/Provider Payment Change]( MA Base!N0033) | OP Facility - Other [Unit Cost/Provider Payment Change]( MA Base!N0034) | Professional [Unit Cost/Provider Payment Change]( MA Base!N0035) | Part B Rx [Unit Cost/Provider Payment Change]( MA Base!N0036) | Other Medicare Covered [Unit Cost/Provider Payment Change]( MA Base!N0037) | Transportation (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0038) | Dental (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0039) | Vision (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0040) | Hearing (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0041) | Health & Education [Unit Cost/Provider Payment Change]( MA Base!N0042) | Other Non-Covered [Unit Cost/Provider Payment Change]( MA Base!N0043) | COB/Subrg. [Unit Cost/Provider Payment Change]( MA Base!N0044) | Inpatient Facility [Unit Cost Adj/Other Factor]( MA Base!O0027) | Skilled Nursing Facility [Unit Cost Adj/Other Factor]( MA Base!O0028) | Home Health [Unit Cost Adj/Other Factor]( MA Base!O0029) | Ambulance [Unit Cost Adj/Other Factor]( MA Base!O0030) | DME/Prosthetics/Supplies [Unit Cost Adj/Other Factor]( MA Base!O0031) | OP Facility - Emergency [Unit Cost Adj/Other Factor]( MA Base!O0032) | OP Facility - Surgery [Unit Cost Adj/Other Factor]( MA Base!O0033) | OP Facility - Other [Unit Cost Adj/Other Factor]( MA Base!O0034) | Professional [Unit Cost Adj/Other Factor]( MA Base!O0035) | Part B Rx [Unit Cost Adj/Other Factor]( MA Base!O0036) | Other Medicare Covered [Unit Cost Adj/Other Factor]( MA Base!O0037) | Transportation (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0038) | Dental (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0039) | Vision (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0040) | Hearing (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0041) | Health & Education [Unit Cost Adj/Other Factor]( MA Base!O0042) | Other Non-Covered [Unit Cost Adj/Other Factor]( MA Base!O0043) | COB/Subrg. [Unit Cost Adj/Other Factor]( MA Base!O0044) | Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) | Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) | Home Health [Additive Adjustment Util/1000]( MA Base!P0029) | Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) | DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) | OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) | OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) | OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) | Professional [Additive Adjustment Util/1000]( MA Base!P0035) | Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) | Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) | Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) | Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) | Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) | Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) | Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) | Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) | Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) | Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) | Home Health [Additive Adjustment PMPM]( MA Base!Q0029) | Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) | DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) | OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) | OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) | OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) | Professional [Additive Adjustment PMPM]( MA Base!Q0035) | Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) | Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) | Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) | Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) | Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) | Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) | Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) | Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) | COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044) | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | H0028 | Local CCP | CHA HMO, INC. | Humana | Humana Inc. | Yes | 100.0 | 41 | 4 | 4 | 4 | 1.041586 | 1.040309 | 1.085316 | 1.085316 | 1.085316 | 1.064892 | 1.058333 | 1.062038 | 1.034823 | 1.038227 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.965442 | 1.000000 | 1.0 | 0.992343 | 1.000000 | 1.0 | 0.987336 | 1.001475 | 1.002775 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.064938 | 1.111958 | 1.047501 | 1.056095 | 1.051343 | 1.025000 | 1.023171 | 1.021300 | 1.024814 | 1.023619 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.946205 | 1.029156 | 1.021117 | 1.021117 | 1.021117 | 0.968596 | 0.973498 | 0.970729 | 0.992119 | 0.991759 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.021212 | 1.029386 | 1.000603 | 1.000322 | 0.999001 | 1.027579 | 1.025603 | 1.026627 | 1.018506 | 1.020768 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.032320 | 1.073888 | 1.095386 | 1.093760 | 1.096505 | 1.014410 | 1.004024 | 1.011607 | 0.970527 | 0.910285 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -12643.602650 | 1908.905341 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -4.830103 | 0.467350 | 1.0 |
| 1 | H0028 | Local CCP | CHA HMO, INC. | Humana | Humana Inc. | Yes | 100.0 | 41 | 4 | 4 | 4 | 1.048467 | 1.033212 | 1.110564 | 1.110564 | 1.110564 | 1.078348 | 1.068757 | 1.072278 | 1.039763 | 1.051768 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.965887 | 1.000000 | 1.0 | 0.992552 | 1.000000 | 1.0 | 0.988270 | 1.000268 | 1.003637 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.086141 | 1.198642 | 1.073244 | 1.146072 | 1.070351 | 1.074450 | 1.025969 | 1.033564 | 1.032510 | 1.024794 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.946038 | 1.035478 | 1.010779 | 1.010779 | 1.010779 | 0.993476 | 0.991236 | 0.992058 | 0.984504 | 0.990402 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.011443 | 1.029042 | 1.007127 | 1.005176 | 1.004994 | 1.023802 | 1.022753 | 1.020853 | 1.017948 | 1.018910 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.034544 | 1.067340 | 1.106669 | 1.098973 | 1.101808 | 1.011492 | 0.996657 | 1.008320 | 0.981439 | 0.923308 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -2860.545342 | 2832.475088 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.000000 | 1.0 | -1.221185 | 0.563870 | 1.0 |
| 2 | H0028 | Local CCP | CHA HMO, INC. | Humana | Humana Inc. | Yes | 100.0 | 41 | 4 | 4 | 4 | 1.035891 | 1.045731 | 1.096176 | 1.096176 | 1.096176 | 1.068046 | 1.064381 | 1.065199 | 1.049049 | 1.062640 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.983970 | 1.038864 | 1.0 | 0.985140 | 1.010346 | 1.0 | 0.999905 | 1.003564 | 0.996684 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.126589 | 1.154194 | 1.011559 | 1.125635 | 1.068715 | 1.053648 | 1.022186 | 1.021385 | 1.015208 | 1.018191 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 0.949851 | 1.034484 | 1.014373 | 1.014373 | 1.014373 | 1.006236 | 1.002903 | 1.003647 | 0.988906 | 1.003002 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.014657 | 1.029904 | 1.005337 | 1.004158 | 1.004711 | 1.028985 | 1.027605 | 1.025852 | 1.019485 | 1.024157 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.009003 | 1.077537 | 1.087700 | 1.080784 | 1.098513 | 0.991736 | 1.019309 | 0.992896 | 0.999983 | 0.955871 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | -81.000569 | 1.0 | 2213.447304 | -19070.361140 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | 1.0 | -0.261382 | 1.0 | 1.697966 | -1.626392 | 1.0 |
This case-study examines the projection assumptions at the county-level.
Note that there are several steps to map the data to FIPs. We took this approach:
ma_5), which contains a mapping of the Bid IDs -> county.ma_1 using the mapped Bid ID <-> FIPs relationship.The resulting dataframe is the chloropleth_outliers.csv, which contains the top 10 service categories for each projection assumption that had the highest variance across all bid IDs.
First, load the FIPs-mapped data.
from urllib.request import urlopen
import requests
import json
from sklearn.preprocessing import MinMaxScaler
# Specialized libraries for geographic analysis
import geopandas as gpd
import branca.colormap as cm
import folium
import branca
county_path = "/mnt/c/users/ScottCampit_nzmndyg/Projects/bid-pricing-tool/analysis/2015/chloropleth_outliers.csv"
county_df = pd.read_csv(county_path)
county_df.head(3)
/tmp/ipykernel_3832/1366550535.py:2: DtypeWarning: Columns (239,240,241,242,264,291,292,293,294,345) have mixed types. Specify dtype option on import or set low_memory=False. county_df = pd.read_csv(county_path)
| Inpatient Facility Utilizers( MA Base!C0027) | Skilled Nursing Facility Utilizers( MA Base!C0028) | Home Health Utilizers( MA Base!C0029) | Ambulance Utilizers( MA Base!C0030) | DME/Prosthetics/Supplies Utilizers( MA Base!C0031) | OP Facility - Emergency Utilizers( MA Base!C0032) | OP Facility - Surgery Utilizers( MA Base!C0033) | OP Facility - Other Utilizers( MA Base!C0034) | Professional Utilizers( MA Base!C0035) | Part B Rx Utilizers( MA Base!C0036) | Other Medicare Covered Utilizers( MA Base!C0037) | Transportation (Non-Covered) Utilizers( MA Base!C0038) | Dental (Non-Covered) Utilizers( MA Base!C0039) | Vision (Non-Covered) Utilizers( MA Base!C0040) | Hearing (Non-Covered) Utilizers( MA Base!C0041) | Health & Education Utilizers( MA Base!C0042) | Other Non-Covered Utilizers( MA Base!C0043) | Contract Number( MA Base!D0005) | Plan ID( MA Base!D0006) | Segment ID( MA Base!D0007) | Contract Year (MA Base!D0008) | Inpatient Facility Net PMPM( MA Base!D0027) | Skilled Nursing Facility Net PMPM( MA Base!D0028) | Home Health Net PMPM( MA Base!D0029) | Ambulance Net PMPM( MA Base!D0030) | DME/Prosthetics/Supplies Net PMPM( MA Base!D0031) | OP Facility - Emergency Net PMPM( MA Base!D0032) | OP Facility - Surgery Net PMPM( MA Base!D0033) | OP Facility - Other Net PMPM( MA Base!D0034) | Professional Net PMPM( MA Base!D0035) | Part B Rx Net PMPM( MA Base!D0036) | Other Medicare Covered Net PMPM( MA Base!D0037) | Transportation (Non-Covered) Net PMPM( MA Base!D0038) | Dental (Non-Covered) Net PMPM( MA Base!D0039) | Vision (Non-Covered) Net PMPM( MA Base!D0040) | Hearing (Non-Covered) Net PMPM( MA Base!D0041) | Health & Education Net PMPM( MA Base!D0042) | Other Non-Covered Net PMPM( MA Base!D0043) | COB/Subrg. (Outside Claim system) Net PMPM( MA Base!D0044) | Net PMPM Total Medical Expenses( MA Base!D0045) | Base Period Summary CMS Revenue ESRD( MA Base!D0055) | Base Period Summary Premium Revenue ESRD( MA Base!D0056) | Base Period Summary Total Revenue ESRD( MA Base!D0057) | Base Period Summary Net Medical Expenses ESRD( MA Base!D0059) | Base Period Summary Member Months ESRD( MA Base!D0061) | Base Period Summary PMPMs: Revenue PMPM ESRD( MA Base!D0064) | Base Period Summary PMPMs: Net Medical PMPM ESRD( MA Base!D0065) | Time Period Definition - Incurred From (MA Base!E0014) | Time Period Definition - Incurred to (MA Base!E0015) | Time Period Definition - Paid through( MA Base!E0016) | Inpatient Facility Cost Sharing( MA Base!E0027) | Skilled Nursing Facility Cost Sharing( MA Base!E0028) | Home Health Cost Sharing( MA Base!E0029) | Ambulance Cost Sharing( MA Base!E0030) | DME/Prosthetics/Supplies Cost Sharing( MA Base!E0031) | OP Facility - Emergency Cost Sharing( MA Base!E0032) | OP Facility - Surgery Cost Sharing( MA Base!E0033) | OP Facility - Other Cost Sharing( MA Base!E0034) | Professional Cost Sharing( MA Base!E0035) | Part B Rx Cost Sharing( MA Base!E0036) | Other Medicare Covered Cost Sharing( MA Base!E0037) | Transportation (Non-Covered) Cost Sharing( MA Base!E0038) | Dental (Non-Covered) Cost Sharing( MA Base!E0039) | Vision (Non-Covered) Cost Sharing( MA Base!E0040) | Hearing (Non-Covered) Cost Sharing( MA Base!E0041) | Health & Education Cost Sharing( MA Base!E0042) | Other Non-Covered Cost Sharing( MA Base!E0043) | COB/Subrg. (Outside Claim system) Cost Sharing( MA Base!E0044) | Net Cost Sharing Total( MA Base!E0045) | Base Period Summary CMS Revenue Hospice( MA Base!E0055) | Base Period Summary Premium Revenue Hospice( MA Base!E0056) | Base Period Summary Total Revenue Hospice( MA Base!E0057) | Base Period Summary Net Medical Expenses Hospice( MA Base!E0059) | Base Period Summary Member Months Hospice( MA Base!E0061) | Base Period Summary PMPMs: Revenue PMPM Hospice( MA Base!E0064) | Base Period Summary PMPMs: Net Medical PMPM Hospice( MA Base!E0065) | Base Period Summary CMS Revenue All Other( MA Base!F0055) | Base Period Summary Premium Revenue All Other( MA Base!F0056) | Base Period Summary Total Revenue All Other( MA Base!F0057) | Base Period Summary Net Medical Expenses All Other( MA Base!F0059) | Base Period Summary Member Months All Other( MA Base!F0061) | Base Period Summary PMPMs: Revenue PMPM All Other( MA Base!F0064) | Base Period Summary PMPMs: Net Medical PMPM All Other( MA Base!F0065) | Organization Name( MA Base!G0005) | Plan Name( MA Base!G0006) | Plan Type( MA Base!G0007) | MA-PD( MA Base!G0008) | Inpatient Facility Util/1000( MA Base!G0027) | Skilled Nursing Facility Util/1000( MA Base!G0028) | Home Health Util/1000( MA Base!G0029) | Ambulance Util/1000( MA Base!G0030) | DME/Prosthetics/Supplies Util/1000( MA Base!G0031) | OP Facility - Emergency Util/1000( MA Base!G0032) | OP Facility - Surgery Util/1000( MA Base!G0033) | OP Facility - Other Util/1000( MA Base!G0034) | Professional Util/1000( MA Base!G0035) | Part B Rx Util/1000( MA Base!G0036) | Other Medicare Covered Util/1000( MA Base!G0037) | Transportation (Non-Covered) Util/1000( MA Base!G0038) | Dental (Non-Covered) Util/1000( MA Base!G0039) | Vision (Non-Covered) Util/1000( MA Base!G0040) | Hearing (Non-Covered) Util/1000( MA Base!G0041) | Health & Education Util/1000( MA Base!G0042) | Other Non-Covered Util/1000( MA Base!G0043) | Base Period Summary CMS Revenue Total( MA Base!G0055) | Base Period Summary Premium Revenue Total( MA Base!G0056) | Base Period Summary Total Revenue Total( MA Base!G0057) | Base Period Summary Net Medical Expenses Total( MA Base!G0059) | Base Period Summary Member Months Total( MA Base!G0061) | Base Period Summary PMPMs: Revenue PMPM Total( MA Base!G0064) | Base Period Summary PMPMs: Net Medical PMPM Total( MA Base!G0065) | Base Period Summary PMPMs: Non-Benefit PMPM Total( MA Base!G0066) | Base Period Summary PMPMs: Gain/(Loss) Margin PMPM Total( MA Base!G0067) | Inpatient Facility Avg Cost( MA Base!H0027) | Skilled Nursing Facility Avg Cost( MA Base!H0028) | Home Health Avg Cost( MA Base!H0029) | Ambulance Avg Cost( MA Base!H0030) | DME/Prosthetics/Supplies Avg Cost( MA Base!H0031) | OP Facility - Emergency Avg Cost( MA Base!H0032) | OP Facility - Surgery Avg Cost( MA Base!H0033) | OP Facility - Other Avg Cost( MA Base!H0034) | Professional Avg Cost( MA Base!H0035) | Part B Rx Avg Cost( MA Base!H0036) | Other Medicare Covered Avg Cost( MA Base!H0037) | Transportation (Non-Covered) Avg Cost( MA Base!H0038) | Dental (Non-Covered) Avg Cost( MA Base!H0039) | Vision (Non-Covered) Avg Cost( MA Base!H0040) | Hearing (Non-Covered) Avg Cost( MA Base!H0041) | Health & Education Avg Cost( MA Base!H0042) | Other Non-Covered Avg Cost( MA Base!H0043) | Total Member Months( MA Base!I0013) | Total Non-ESRD Risk Score( MA Base!I0014) | Total Completion Factor( MA Base!I0015) | Inpatient Facility Allowed PMPM( MA Base!I0027) | Skilled Nursing Facility Allowed PMPM( MA Base!I0028) | Home Health Allowed PMPM( MA Base!I0029) | Ambulance Allowed PMPM( MA Base!I0030) | DME/Prosthetics/Supplies Allowed PMPM( MA Base!I0031) | OP Facility - Emergency Allowed PMPM( MA Base!I0032) | OP Facility - Surgery Allowed PMPM( MA Base!I0033) | OP Facility - Other Allowed PMPM( MA Base!I0034) | Professional Allowed PMPM( MA Base!I0035) | Part B Rx Allowed PMPM( MA Base!I0036) | Other Medicare Covered Allowed PMPM( MA Base!I0037) | Transportation (Non-Covered) Allowed PMPM( MA Base!I0038) | Dental (Non-Covered) Allowed PMPM( MA Base!I0039) | Vision (Non-Covered) Allowed PMPM( MA Base!I0040) | Hearing (Non-Covered) Allowed PMPM( MA Base!I0041) | Health & Education Allowed PMPM( MA Base!I0042) | Other Non-Covered Allowed PMPM( MA Base!I0043) | COB/Subrg. Allowed PMPM( MA Base!I0044) | Total Medical Expenses Allowed PMPM( MA Base!I0045) | Sub-Total Medicare-covered Services Allowed PMPM( MA Base!I0047) | Non-DE# Member Months( MA Base!J0013) | Non-DE# Risk Score( MA Base!J0014) | Inpatient Facility [Util/1000 Trend]( MA Base!J0027) | Skilled Nursing Facility [Util/1000 Trend]( MA Base!J0028) | Home Health [Util/1000 Trend]( MA Base!J0029) | Ambulance [Util/1000 Trend]( MA Base!J0030) | DME/Prosthetics/Supplies [Util/1000 Trend]( MA Base!J0031) | OP Facility - Emergency [Util/1000 Trend]( MA Base!J0032) | OP Facility - Surgery [Util/1000 Trend]( MA Base!J0033) | OP Facility - Other [Util/1000 Trend]( MA Base!J0034) | Professional [Util/1000 Trend]( MA Base!J0035) | Part B Rx [Util/1000 Trend]( MA Base!J0036) | Other Medicare Covered [Util/1000 Trend]( MA Base!J0037) | Transportation (Non-Covered) [Util/1000 Trend]( MA Base!J0038) | Dental (Non-Covered) [Util/1000 Trend]( MA Base!J0039) | Vision (Non-Covered) [Util/1000 Trend]( MA Base!J0040) | Hearing (Non-Covered) [Util/1000 Trend]( MA Base!J0041) | Health & Education [Util/1000 Trend]( MA Base!J0042) | Other Non-Covered [Util/1000 Trend]( MA Base!J0043) | COB/Subrg. [Util/1000 Trend]( MA Base!J0044) | Enrollee Type( MA Base!K0005) | MA Region( MA Base!K0006) | Act. Swap/Equiv. Indicator( MA Base!K0007) | SNP Indicator( MA Base!K0008) | DE# Member Months( MA Base!K0013) | DE# Risk Score( MA Base!K0014) | Inpatient Facility [Benefit Plan Change]( MA Base!K0027) | Skilled Nursing Facility [Benefit Plan Change]( MA Base!K0028) | Home Health [Benefit Plan Change]( MA Base!K0029) | Ambulance [Benefit Plan Change]( MA Base!K0030) | DME/Prosthetics/Supplies [Benefit Plan Change]( MA Base!K0031) | OP Facility - Emergency [Benefit Plan Change]( MA Base!K0032) | OP Facility - Surgery [Benefit Plan Change]( MA Base!K0033) | OP Facility - Other [Benefit Plan Change]( MA Base!K0034) | Professional [Benefit Plan Change]( MA Base!K0035) | Part B Rx [Benefit Plan Change]( MA Base!K0036) | Other Medicare Covered [Benefit Plan Change]( MA Base!K0037) | Transportation (Non-Covered) [Benefit Plan Change]( MA Base!K0038) | Dental (Non-Covered) [Benefit Plan Change]( MA Base!K0039) | Vision (Non-Covered) [Benefit Plan Change]( MA Base!K0040) | Hearing (Non-Covered) [Benefit Plan Change]( MA Base!K0041) | Health & Education [Benefit Plan Change]( MA Base!K0042) | Other Non-Covered [Benefit Plan Change]( MA Base!K0043) | COB/Subrg. [Benefit Plan Change]( MA Base!K0044) | Non-Benefit Expenses: Marketing & Sales( MA Base!K0056) | Non-Benefit Expenses: Direct Admin( MA Base!K0057) | Non-Benefit Expenses: Indirect Admin( MA Base!K0058) | Non-Benefit Expenses: Net Cost of Private Reinsurance( MA Base!K0059) | Insurer Fees( MA Base!K0060) | Non-Benefit Expenses: Total Non-Benefit Expenses( MA Base!K0062) | Inpatient Facility [Population Change]( MA Base!L0027) | Skilled Nursing Facility [Population Change]( MA Base!L0028) | Home Health [Population Change]( MA Base!L0029) | Ambulance [Population Change]( MA Base!L0030) | DME/Prosthetics/Supplies [Population Change]( MA Base!L0031) | OP Facility - Emergency [Population Change]( MA Base!L0032) | OP Facility - Surgery [Population Change]( MA Base!L0033) | OP Facility - Other [Population Change]( MA Base!L0034) | Professional [Population Change]( MA Base!L0035) | Part B Rx [Population Change]( MA Base!L0036) | Other Medicare Covered [Population Change]( MA Base!L0037) | Transportation (Non-Covered) [Population Change]( MA Base!L0038) | Dental (Non-Covered) [Population Change]( MA Base!L0039) | Vision (Non-Covered) [Population Change]( MA Base!L0040) | Hearing (Non-Covered) [Population Change]( MA Base!L0041) | Health & Education [Population Change]( MA Base!L0042) | Other Non-Covered [Population Change]( MA Base!L0043) | COB/Subrg. [Population Change]( MA Base!L0044) | Inpatient Facility [Other Factor]( MA Base!M0027) | Skilled Nursing Facility [Other Factor]( MA Base!M0028) | Home Health [Other Factor]( MA Base!M0029) | Ambulance [Other Factor]( MA Base!M0030) | DME/Prosthetics/Supplies [Other Factor]( MA Base!M0031) | OP Facility - Emergency [Other Factor]( MA Base!M0032) | OP Facility - Surgery [Other Factor]( MA Base!M0033) | OP Facility - Other [Other Factor]( MA Base!M0034) | Professional [Other Factor]( MA Base!M0035) | Part B Rx [Other Factor]( MA Base!M0036) | Other Medicare Covered [Other Factor]( MA Base!M0037) | Transportation (Non-Covered) [Other Factor]( MA Base!M0038) | Dental (Non-Covered) [Other Factor]( MA Base!M0039) | Vision (Non-Covered) [Other Factor]( MA Base!M0040) | Hearing (Non-Covered) [Other Factor]( MA Base!M0041) | Health & Education [Other Factor]( MA Base!M0042) | Other Non-Covered [Other Factor]( MA Base!M0043) | COB/Subrg. [Other Factor]( MA Base!M0044) | Contract Plan ID - a( MA Base!N0014) | Contract Plan ID - b( MA Base!N0015) | Contract Plan ID - c( MA Base!N0016) | Contract Plan ID - d( MA Base!N0017) | Inpatient Facility [Unit Cost/Provider Payment Change]( MA Base!N0027) | Skilled Nursing Facility [Unit Cost/Provider Payment Change]( MA Base!N0028) | Home Health [Unit Cost/Provider Payment Change]( MA Base!N0029) | Ambulance [Unit Cost/Provider Payment Change]( MA Base!N0030) | DME/Prosthetics/Supplies [Unit Cost/Provider Payment Change]( MA Base!N0031) | OP Facility - Emergency [Unit Cost/Provider Payment Change]( MA Base!N0032) | OP Facility - Surgery [Unit Cost/Provider Payment Change]( MA Base!N0033) | OP Facility - Other [Unit Cost/Provider Payment Change]( MA Base!N0034) | Professional [Unit Cost/Provider Payment Change]( MA Base!N0035) | Part B Rx [Unit Cost/Provider Payment Change]( MA Base!N0036) | Other Medicare Covered [Unit Cost/Provider Payment Change]( MA Base!N0037) | Transportation (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0038) | Dental (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0039) | Vision (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0040) | Hearing (Non-Covered) [Unit Cost/Provider Payment Change]( MA Base!N0041) | Health & Education [Unit Cost/Provider Payment Change]( MA Base!N0042) | Other Non-Covered [Unit Cost/Provider Payment Change]( MA Base!N0043) | COB/Subrg. [Unit Cost/Provider Payment Change]( MA Base!N0044) | Region Name( MA Base!O0005) | Region Name Sub Category 1( MA Base!O0006) | Region Name Sub Category 2( MA Base!O0007) | SNP Type( MA Base!O0008) | Member Month Percentage - a( MA Base!O0014) | Member Month Percentage - b( MA Base!O0015) | Member Month Percentage - c( MA Base!O0016) | Member Month Percentage - d( MA Base!O0017) | Inpatient Facility [Unit Cost Adj/Other Factor]( MA Base!O0027) | Skilled Nursing Facility [Unit Cost Adj/Other Factor]( MA Base!O0028) | Home Health [Unit Cost Adj/Other Factor]( MA Base!O0029) | Ambulance [Unit Cost Adj/Other Factor]( MA Base!O0030) | DME/Prosthetics/Supplies [Unit Cost Adj/Other Factor]( MA Base!O0031) | OP Facility - Emergency [Unit Cost Adj/Other Factor]( MA Base!O0032) | OP Facility - Surgery [Unit Cost Adj/Other Factor]( MA Base!O0033) | OP Facility - Other [Unit Cost Adj/Other Factor]( MA Base!O0034) | Professional [Unit Cost Adj/Other Factor]( MA Base!O0035) | Part B Rx [Unit Cost Adj/Other Factor]( MA Base!O0036) | Other Medicare Covered [Unit Cost Adj/Other Factor]( MA Base!O0037) | Transportation (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0038) | Dental (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0039) | Vision (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0040) | Hearing (Non-Covered) [Unit Cost Adj/Other Factor]( MA Base!O0041) | Health & Education [Unit Cost Adj/Other Factor]( MA Base!O0042) | Other Non-Covered [Unit Cost Adj/Other Factor]( MA Base!O0043) | COB/Subrg. [Unit Cost Adj/Other Factor]( MA Base!O0044) | Non-Benefit Expenses: Gain(Loss) Margin( MA Base!O0055) | Percent of Revenue: Net Medical Expenses( MA Base!O0058) | Percent of Revenue: Non-Benefit Expenses( MA Base!O0059) | Percent of Revenue: Gain/(Loss) Margin( MA Base!O0060) | Contract Plan ID a( MA Base!P0014) | Contract Plan ID b( MA Base!P0015) | Contract Plan ID c( MA Base!P0016) | Contract Plan ID d( MA Base!P0017) | Inpatient Facility [Additive Adjustment Util/1000]( MA Base!P0027) | Skilled Nursing Facility [Additive Adjustment Util/1000]( MA Base!P0028) | Home Health [Additive Adjustment Util/1000]( MA Base!P0029) | Ambulance [Additive Adjustment Util/1000]( MA Base!P0030) | DME/Prosthetics/Supplies [Additive Adjustment Util/1000]( MA Base!P0031) | OP Facility - Emergency [Additive Adjustment Util/1000]( MA Base!P0032) | OP Facility - Surgery [Additive Adjustment Util/1000]( MA Base!P0033) | OP Facility - Other [Additive Adjustment Util/1000]( MA Base!P0034) | Professional [Additive Adjustment Util/1000]( MA Base!P0035) | Part B Rx [Additive Adjustment Util/1000]( MA Base!P0036) | Other Medicare Covered [Additive Adjustment Util/1000]( MA Base!P0037) | Transportation (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0038) | Dental (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0039) | Vision (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0040) | Hearing (Non-Covered) [Additive Adjustment Util/1000]( MA Base!P0041) | Health & Education [Additive Adjustment Util/1000]( MA Base!P0042) | Other Non-Covered [Additive Adjustment Util/1000]( MA Base!P0043) | Medicaid Revenue( MA Base!P0063) | Medicaid Cost( MA Base!P0064) | Benefit Expenses( MA Base!P0065) | Non-benefit Expenses( MA Base!P0066) | Adjusted GLM( MA Base!P0067) | EGWP:( MA Base!Q0008) | Member Months a( MA Base!Q0014) | Member Months b( MA Base!Q0015) | Member Months c( MA Base!Q0016) | Member Months d( MA Base!Q0017) | Inpatient Facility [Additive Adjustment PMPM]( MA Base!Q0027) | Skilled Nursing Facility [Additive Adjustment PMPM]( MA Base!Q0028) | Home Health [Additive Adjustment PMPM]( MA Base!Q0029) | Ambulance [Additive Adjustment PMPM]( MA Base!Q0030) | DME/Prosthetics/Supplies [Additive Adjustment PMPM]( MA Base!Q0031) | OP Facility - Emergency [Additive Adjustment PMPM]( MA Base!Q0032) | OP Facility - Surgery [Additive Adjustment PMPM]( MA Base!Q0033) | OP Facility - Other [Additive Adjustment PMPM]( MA Base!Q0034) | Professional [Additive Adjustment PMPM]( MA Base!Q0035) | Part B Rx [Additive Adjustment PMPM]( MA Base!Q0036) | Other Medicare Covered [Additive Adjustment PMPM]( MA Base!Q0037) | Transportation (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0038) | Dental (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0039) | Vision (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0040) | Hearing (Non-Covered) [Additive Adjustment PMPM]( MA Base!Q0041) | Health & Education [Additive Adjustment PMPM]( MA Base!Q0042) | Other Non-Covered [Additive Adjustment PMPM]( MA Base!Q0043) | COB/Subrg. [Additive Adjustment PMPM]( MA Base!Q0044) | bid_id | HMO ID | Organization name | Parent Org | Dec2015 enrollment | 2015 Part C Star rating Spring | State County Code 1( MA Bnchmk!B0039) | State 1( MA Bnchmk!C0039) | County Name 1( MA Bnchmk!D0039) | County 1 - Projected Member Months( MA Bnchmk!E0039) | County 1 - Projected Risk Factors( MA Bnchmk!F0039) | County 1 - Plan Provided ISAR Factors for Risk Rates( MA Bnchmk!G0039) | County 1 - MA Risk Ratebook Unadjusted( MA Bnchmk!H0039) | County 1 - MA Risk Ratebook Risk-adjusted( MA Bnchmk!I0039) | County 1 - ISAR Scale( MA Bnchmk!J0039) | County 1 - ISAR-adjusted Bid( MA Bnchmk!K0039) | County 1 - Risk Payment Rate A Only( MA Bnchmk!L0039) | County 1 - Risk Payment Rate B Only( MA Bnchmk!M0039) | County 1 - Original Medicare Cost Sharing Inpatient( MA Bnchmk!N0039) | County 1 - Original Medicare Cost Sharing SNF( MA Bnchmk!O0039) | County 1 - Weighted Average for Service Area - Original Medicare Cost Sharing Other Pt B( MA Bnchmk!P0039) | County 1 - FFS costs to weight Inpatient( MA Bnchmk!Q0039) | County 1 - FFS costs to weight SNF( MA Bnchmk!R0039) | County 1 - FFS costs to weight Other Pt B( MA Bnchmk!S0039) | County 1 - Metropolitan Statistical Area MM( MA Bnchmk!T0039) | County 1 - Metropolitan Statistical Area MSA Name( MA Bnchmk!U0039) | SSA State county code | FIPS State county code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 4754.0 | 919.0 | 2670.0 | 3548.0 | 12303.0 | 6726.0 | 2140.0 | 24508.0 | 25404.0 | 11604.0 | 10165.0 | 721.0 | 0.0 | 4269.0 | 53.0 | 715.0 | 0.0 | H0151 | 1 | 0 | 2015 | 210.7875 | 22.178402 | 29.340429 | 7.272934 | 27.367561 | 18.218614 | 8.847904 | 103.794439 | 101.861536 | 28.230852 | 4.426344 | 2.692925 | 0.0 | 1.25962 | 0.000754 | 0.329534 | 0.0 | 0.0 | 566.6094 | 14939636.9 | 0.0 | 14939636.9 | 12576335.81 | 2156.688732 | 6927.1178 | 5831.3171 | 1/1/2013 | 12/31/2013 | 3/21/2014 | 22.5058 | 5.164165 | 0.0 | 3.401596 | 2.98235 | 2.032848 | 1.678272 | 14.930552 | 19.213499 | 5.419814 | 0.98473 | 0.0 | 0.0 | 0.533616 | 0.100347 | 0.0 | 0.0 | 0 | 78.9476 | 143569.0667 | 0.0 | 143569.0667 | 207433.0258 | 2726.134619 | 52.664 | 76.0905 | 214510435.2 | 0.0 | 214510435.2 | 170470426.8 | 300860.58 | 712.9895 | 566.6094 | UNITEDHEALTHCARE OF ALABAMA, INC. | AARP MedicareComplete Plan 1 (HMO) | HMO | Y | 1833.067126 | 1002.050819 | 2328.10614 | 244.687388 | 3622.421446 | 477.382747 | 120.444486 | 21789.27608 | 13485.1476 | 2263.18779 | 692.747326 | 229.42188 | 0.0 | 240.483809 | 3.711546 | 40.683296 | 0.0 | 229593641.2 | 0.0 | 229593641.2 | 183254195.6 | 305743.4034 | 750.9357 | 599.3725 | 76.0188 | 75.5444 | 1527.233 | 327.439285 | 151.232428 | 523.502068 | 100.540187 | 509.06227 | 1048.733031 | 65.385371 | 107.740787 | 178.424434 | 93.732414 | 140.854477 | 0.0 | 89.481454 | 326.876068 | 97.199648 | 0.0 | 300860.58 | 1.004905 | 1.021063 | 233.2934 | 27.342567 | 29.340429 | 10.674529 | 30.349911 | 20.251462 | 10.526176 | 118.724992 | 121.075034 | 33.650667 | 5.411073 | 2.692925 | 0.0 | 1.793237 | 0.101101 | 0.329534 | 0.0 | 0.0 | 645.557 | 640.6402 | 266936.0164 | 0.961066 | 0.937118 | 1.007967 | 1.061817 | 1.061817 | 1.061817 | 1.061817 | 1.057846 | 1.016233 | 1.004594 | 1.009511 | 1.008621 | 1.061817 | 1.0 | 1.008621 | 1.008621 | 1.008621 | 1.0 | 1.0 | A/B | 0 | N | N | 33924.56363 | 1.349853 | 0.995 | 1.028 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 1.0 | 0.98 | 0.98 | 0.98 | 1.0 | 1.0 | 5711524.233 | 12346048.07 | 5184675.755 | 0.0 | 0.0 | 23242248.06 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 1.0 | 0.980687 | 0.980687 | 0.980687 | 1.0 | 1.0 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.0 | 1.00015 | 1.00015 | 1.00015 | 1.0 | 1.0 | H0151-001 | 0 | 0 | 0 | 1.026194 | 1.004906 | 1.001512 | 1.003724 | 0.762409 | 1.03258 | 1.004045 | 1.034831 | 1.030911 | 1.012546 | 0.999273 | 1.014148 | 1.0 | 1.059446 | 0.828348 | 1.011256 | 1.0 | 1.0 | 0 | 0 | 0 | 0 | 300860.58 | 0.0 | 0.0 | 0 | 1.048237 | 1.006122 | 1.019516 | 1.019516 | 1.019516 | 1.019516 | 1.010503 | 1.007876 | 0.997158 | 1.000264 | 0.996563 | 1.019516 | 1.0 | 0.996563 | 0.996563 | 0.996563 | 1.0 | 1.0 | 23097197.52 | 0.798168 | 0.101232 | 0.1006 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | -8.9328 | 0.0 | 0.0 | -234.15643 | 0.0 | 0.0 | 27.801641 | 0.0 | 0.0 | 2035473.818 | 4202603.765 | 3746630.405 | 455973.3605 | 20930067.57 | N | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | -0.034265 | 0.0 | 0.0 | -2.841782 | 0.0 | 0.0 | 0.406603 | 0.0 | 0.0 | 0.0 | H0151_001_0 | H0151 | UNITEDHEALTHCARE OF ALABAMA, INC. | UnitedHealth Group, Inc. | 33636 | 3 | 1480 | AL | Mobile | 37415.17777 | 1.106654 | 0 | 789.21 | 873.382169 | 1.065793 | 680.025285 | 315.315285 | 364.71 | 0.092728 | 0.167257 | 0.2125 | 227.87 | 50.82 | 448.21 | 37415.17777 | Mobile, AL | 1480 | 1097 |
| 1 | 4754.0 | 919.0 | 2670.0 | 3548.0 | 12303.0 | 6726.0 | 2140.0 | 24508.0 | 25404.0 | 11604.0 | 10165.0 | 721.0 | 0.0 | 4269.0 | 53.0 | 715.0 | 0.0 | H0151 | 1 | 0 | 2015 | 210.7875 | 22.178402 | 29.340429 | 7.272934 | 27.367561 | 18.218614 | 8.847904 | 103.794439 | 101.861536 | 28.230852 | 4.426344 | 2.692925 | 0.0 | 1.25962 | 0.000754 | 0.329534 | 0.0 | 0.0 | 566.6094 | 14939636.9 | 0.0 | 14939636.9 | 12576335.81 | 2156.688732 | 6927.1178 | 5831.3171 | 1/1/2013 | 12/31/2013 | 3/21/2014 | 22.5058 | 5.164165 | 0.0 | 3.401596 | 2.98235 | 2.032848 | 1.678272 | 14.930552 | 19.213499 | 5.419814 | 0.98473 | 0.0 | 0.0 | 0.533616 | 0.100347 | 0.0 | 0.0 | 0 | 78.9476 | 143569.0667 | 0.0 | 143569.0667 | 207433.0258 | 2726.134619 | 52.664 | 76.0905 | 214510435.2 | 0.0 | 214510435.2 | 170470426.8 | 300860.58 | 712.9895 | 566.6094 | UNITEDHEALTHCARE OF ALABAMA, INC. | AARP MedicareComplete Plan 1 (HMO) | HMO | Y | 1833.067126 | 1002.050819 | 2328.10614 | 244.687388 | 3622.421446 | 477.382747 | 120.444486 | 21789.27608 | 13485.1476 | 2263.18779 | 692.747326 | 229.42188 | 0.0 | 240.483809 | 3.711546 | 40.683296 | 0.0 | 229593641.2 | 0.0 | 229593641.2 | 183254195.6 | 305743.4034 | 750.9357 | 599.3725 | 76.0188 | 75.5444 | 1527.233 | 327.439285 | 151.232428 | 523.502068 | 100.540187 | 509.06227 | 1048.733031 | 65.385371 | 107.740787 | 178.424434 | 93.732414 | 140.854477 | 0.0 | 89.481454 | 326.876068 | 97.199648 | 0.0 | 300860.58 | 1.004905 | 1.021063 | 233.2934 | 27.342567 | 29.340429 | 10.674529 | 30.349911 | 20.251462 | 10.526176 | 118.724992 | 121.075034 | 33.650667 | 5.411073 | 2.692925 | 0.0 | 1.793237 | 0.101101 | 0.329534 | 0.0 | 0.0 | 645.557 | 640.6402 | 266936.0164 | 0.961066 | 0.937118 | 1.007967 | 1.061817 | 1.061817 | 1.061817 | 1.061817 | 1.057846 | 1.016233 | 1.004594 | 1.009511 | 1.008621 | 1.061817 | 1.0 | 1.008621 | 1.008621 | 1.008621 | 1.0 | 1.0 | A/B | 0 | N | N | 33924.56363 | 1.349853 | 0.995 | 1.028 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 1.0 | 0.98 | 0.98 | 0.98 | 1.0 | 1.0 | 5711524.233 | 12346048.07 | 5184675.755 | 0.0 | 0.0 | 23242248.06 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 1.0 | 0.980687 | 0.980687 | 0.980687 | 1.0 | 1.0 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.0 | 1.00015 | 1.00015 | 1.00015 | 1.0 | 1.0 | H0151-001 | 0 | 0 | 0 | 1.026194 | 1.004906 | 1.001512 | 1.003724 | 0.762409 | 1.03258 | 1.004045 | 1.034831 | 1.030911 | 1.012546 | 0.999273 | 1.014148 | 1.0 | 1.059446 | 0.828348 | 1.011256 | 1.0 | 1.0 | 0 | 0 | 0 | 0 | 300860.58 | 0.0 | 0.0 | 0 | 1.048237 | 1.006122 | 1.019516 | 1.019516 | 1.019516 | 1.019516 | 1.010503 | 1.007876 | 0.997158 | 1.000264 | 0.996563 | 1.019516 | 1.0 | 0.996563 | 0.996563 | 0.996563 | 1.0 | 1.0 | 23097197.52 | 0.798168 | 0.101232 | 0.1006 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | -8.9328 | 0.0 | 0.0 | -234.15643 | 0.0 | 0.0 | 27.801641 | 0.0 | 0.0 | 2035473.818 | 4202603.765 | 3746630.405 | 455973.3605 | 20930067.57 | N | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | -0.034265 | 0.0 | 0.0 | -2.841782 | 0.0 | 0.0 | 0.406603 | 0.0 | 0.0 | 0.0 | H0151_001_0 | H0151 | UNITEDHEALTHCARE OF ALABAMA, INC. | UnitedHealth Group, Inc. | 33636 | 3 | 1010 | AL | Baldwin | 24932.91624 | 1.106654 | 0 | 723.94 | 801.150882 | 0.977649 | 623.785184 | 289.235184 | 334.55 | 0.092728 | 0.167257 | 0.2125 | 227.87 | 50.82 | 448.21 | 28108.18573 | Not in a Metro Area | 1010 | 1003 |
| 2 | 4754.0 | 919.0 | 2670.0 | 3548.0 | 12303.0 | 6726.0 | 2140.0 | 24508.0 | 25404.0 | 11604.0 | 10165.0 | 721.0 | 0.0 | 4269.0 | 53.0 | 715.0 | 0.0 | H0151 | 1 | 0 | 2015 | 210.7875 | 22.178402 | 29.340429 | 7.272934 | 27.367561 | 18.218614 | 8.847904 | 103.794439 | 101.861536 | 28.230852 | 4.426344 | 2.692925 | 0.0 | 1.25962 | 0.000754 | 0.329534 | 0.0 | 0.0 | 566.6094 | 14939636.9 | 0.0 | 14939636.9 | 12576335.81 | 2156.688732 | 6927.1178 | 5831.3171 | 1/1/2013 | 12/31/2013 | 3/21/2014 | 22.5058 | 5.164165 | 0.0 | 3.401596 | 2.98235 | 2.032848 | 1.678272 | 14.930552 | 19.213499 | 5.419814 | 0.98473 | 0.0 | 0.0 | 0.533616 | 0.100347 | 0.0 | 0.0 | 0 | 78.9476 | 143569.0667 | 0.0 | 143569.0667 | 207433.0258 | 2726.134619 | 52.664 | 76.0905 | 214510435.2 | 0.0 | 214510435.2 | 170470426.8 | 300860.58 | 712.9895 | 566.6094 | UNITEDHEALTHCARE OF ALABAMA, INC. | AARP MedicareComplete Plan 1 (HMO) | HMO | Y | 1833.067126 | 1002.050819 | 2328.10614 | 244.687388 | 3622.421446 | 477.382747 | 120.444486 | 21789.27608 | 13485.1476 | 2263.18779 | 692.747326 | 229.42188 | 0.0 | 240.483809 | 3.711546 | 40.683296 | 0.0 | 229593641.2 | 0.0 | 229593641.2 | 183254195.6 | 305743.4034 | 750.9357 | 599.3725 | 76.0188 | 75.5444 | 1527.233 | 327.439285 | 151.232428 | 523.502068 | 100.540187 | 509.06227 | 1048.733031 | 65.385371 | 107.740787 | 178.424434 | 93.732414 | 140.854477 | 0.0 | 89.481454 | 326.876068 | 97.199648 | 0.0 | 300860.58 | 1.004905 | 1.021063 | 233.2934 | 27.342567 | 29.340429 | 10.674529 | 30.349911 | 20.251462 | 10.526176 | 118.724992 | 121.075034 | 33.650667 | 5.411073 | 2.692925 | 0.0 | 1.793237 | 0.101101 | 0.329534 | 0.0 | 0.0 | 645.557 | 640.6402 | 266936.0164 | 0.961066 | 0.937118 | 1.007967 | 1.061817 | 1.061817 | 1.061817 | 1.061817 | 1.057846 | 1.016233 | 1.004594 | 1.009511 | 1.008621 | 1.061817 | 1.0 | 1.008621 | 1.008621 | 1.008621 | 1.0 | 1.0 | A/B | 0 | N | N | 33924.56363 | 1.349853 | 0.995 | 1.028 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 0.98 | 1.0 | 0.98 | 0.98 | 0.98 | 1.0 | 1.0 | 5711524.233 | 12346048.07 | 5184675.755 | 0.0 | 0.0 | 23242248.06 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 0.980687 | 1.0 | 0.980687 | 0.980687 | 0.980687 | 1.0 | 1.0 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.00015 | 1.0 | 1.00015 | 1.00015 | 1.00015 | 1.0 | 1.0 | H0151-001 | 0 | 0 | 0 | 1.026194 | 1.004906 | 1.001512 | 1.003724 | 0.762409 | 1.03258 | 1.004045 | 1.034831 | 1.030911 | 1.012546 | 0.999273 | 1.014148 | 1.0 | 1.059446 | 0.828348 | 1.011256 | 1.0 | 1.0 | 0 | 0 | 0 | 0 | 300860.58 | 0.0 | 0.0 | 0 | 1.048237 | 1.006122 | 1.019516 | 1.019516 | 1.019516 | 1.019516 | 1.010503 | 1.007876 | 0.997158 | 1.000264 | 0.996563 | 1.019516 | 1.0 | 0.996563 | 0.996563 | 0.996563 | 1.0 | 1.0 | 23097197.52 | 0.798168 | 0.101232 | 0.1006 | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | -8.9328 | 0.0 | 0.0 | -234.15643 | 0.0 | 0.0 | 27.801641 | 0.0 | 0.0 | 2035473.818 | 4202603.765 | 3746630.405 | 455973.3605 | 20930067.57 | N | 0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | -0.034265 | 0.0 | 0.0 | -2.841782 | 0.0 | 0.0 | 0.406603 | 0.0 | 0.0 | 0.0 | H0151_001_0 | H0151 | UNITEDHEALTHCARE OF ALABAMA, INC. | UnitedHealth Group, Inc. | 33636 | 3 | 1500 | AL | Montgomery | 23053.52626 | 1.106654 | 0 | 702.27 | 777.169696 | 0.948384 | 605.113160 | 280.583160 | 324.53 | 0.092728 | 0.167257 | 0.2125 | 227.87 | 50.82 | 448.21 | 40515.07715 | Montgomery, AL | 1500 | 1101 |
To use the library folium, we need this JSON file that contains the coordinates of each county that can be mapped to... well, the U.S. map.
url = "https://raw.githubusercontent.com/python-visualization/folium/master/examples/data"
county_geo = f"{url}/us_counties_20m_topo.json"
geoJSON_df = gpd.read_file(county_geo)
geoJSON_df['id'] = geoJSON_df["id"].str[-5:].astype(int)
geoJSON_df.head(3)
| id | geometry | |
|---|---|---|
| 0 | 1001 | POLYGON ((-86.49932 32.34394, -86.71470 32.402... |
| 1 | 1009 | POLYGON ((-86.57112 33.76632, -86.75060 33.841... |
| 2 | 1017 | POLYGON ((-85.17117 32.86797, -85.13527 32.771... |
We'll define some functions we need to filter and scale the dataset.
def filter_by_mco(df:pd.DataFrame, mco_type:str) -> pd.DataFrame:
"""Filter by plan type"""
if mco_type != 'All':
mco_df = df.loc[df['Plan Type( MA Base!G0007)'] == mco_type]
else:
mco_df = df
return mco_df
def filter_by_org(df:pd.DataFrame, org:str) -> pd.DataFrame:
"""Filter by organization name"""
if org != 'All':
org_df = df.loc[df['Organization Name( MA Base!G0005)'] == org]
else:
org_df = df
return org_df
def make_filtered_df(df:pd.DataFrame, mco_type:str, org:str) -> pd.DataFrame:
"""Wrap the filter by plan type and organization functions"""
mco_df = filter_by_mco(df, mco_type)
return filter_by_org(mco_df, org)
def filter_by_feat(df:pd.DataFrame, feat_of_interest:str) -> pd.DataFrame:
"""Filter by projection assumption"""
return df.groupby("FIPS State county code")[feat_of_interest].mean()
def minmaxscale(df:pd.DataFrame) -> pd.DataFrame:
"""Performs min-max scaling across all data."""
min_val = df.min()
max_val = df.max()
return (df - min_val) / (max_val - min_val)
def make_colorscale_df(df:pd.DataFrame, mco_type:str, org:str, feat_of_interest:str) -> pd.DataFrame:
"""Create a dataframe that will be used as the color scale."""
mco_df = filter_by_mco(df, mco_type)
org_df = filter_by_org(mco_df, org)
feat_df = filter_by_feat(org_df, feat_of_interest)
return minmaxscale(feat_df)
def create_overlay_data(geojson_df:pd.DataFrame, bpt_df:pd.DataFrame, feat_of_interest:str) -> pd.DataFrame:
"""Take the GeoJSON coordinates and the bid data, and perform a merge by FIPS"""
tmp_df = bpt_df[['State 1( MA Bnchmk!C0039)',
'County Name 1( MA Bnchmk!D0039)',
'FIPS State county code',
feat_of_interest]]
tmp_df = tmp_df.groupby(['State 1( MA Bnchmk!C0039)',
'County Name 1( MA Bnchmk!D0039)',
'FIPS State county code'])[feat_of_interest].mean().reset_index()
return pd.merge(geojson_df, tmp_df,
how='inner',
left_on='id', right_on='FIPS State county code')
colorscale = branca.colormap.linear.YlOrRd_09.scale()
def style_function(feature):
"""Note: color_df is declared outside of the function scope."""
q = color_df.get(key=int(feature["id"][-5:]), default=None)
return {
"fillOpacity": 0.5,
"weight": 0,
"fillColor": "#black" if q is None else colorscale(q),
}
def construct_map(county_json:dict, color_scheme, hover_data, feat_of_interest, alias):
# Instantiate map object
m = folium.Map(location=[37.0902, -95.7129],
tiles="cartodbpositron",
zoom_start=3)
# Colorbar
colormap = cm.linear.YlOrRd_09.scale(0, 1)
colormap.caption = color_scheme.name
m.add_child(colormap)
# Create county-level visualization
folium.TopoJson(
data=json.loads(requests.get(county_json).text),
object_path="objects.us_counties_20m",
style_function=style_function
).add_to(m)
# Add hover attributes
n = folium.features.GeoJson(
data=hover_data.to_json(),
style_function=style_function_hover,
control=False,
highlight_function=highlight_function,
tooltip=folium.features.GeoJsonTooltip(
fields=['State 1( MA Bnchmk!C0039)',
'County Name 1( MA Bnchmk!D0039)',
feat_of_interest],
aliases=['State: ',
'County: ',
alias],
style=("background-color: white; \
color: #333333; \
font-family: arial; \
font-size: 12px; \
padding: 10px;")
)
)
# Add on the hover to the original map
m.add_child(n)
m.keep_in_front(n)
return(m)
The code below all creates parameters for data visualization, such as the hover and the highlighted data.
Below, we're visualizing the Inpatient Facility Utility.
The object m is the folium object that has the mappings.
style_function_hover = lambda x: {'fillColor': '#ffffff',
'color':'#000000',
'fillOpacity': 0.1,
'weight': 0.1}
highlight_function = lambda x: {'fillColor': '#000000',
'color':'#000000',
'fillOpacity': 0.50,
'weight': 0.1}
color_df = make_colorscale_df(county_df, "All", "All", "Inpatient Facility Utilizers( MA Base!C0027)")
org_mco_df = make_filtered_df(county_df, "All", "All")
fulldf = create_overlay_data(geojson_df=geoJSON_df,
bpt_df=org_mco_df,
feat_of_interest='Inpatient Facility Utilizers( MA Base!C0027)')
m = construct_map(county_json=county_geo, color_scheme=color_df, hover_data=fulldf, feat_of_interest='Inpatient Facility Utilizers( MA Base!C0027)', alias='Inpatient Facility: ')
m
bpt.py) that allows the analyst to access the zipped data objects and read them into Pandas dataframes for downstream analyses.